SQL query to select at most one valid row by date w.r.t. foreign key

Question

I have a table (SQL Server 2005) with the following columns:

State:
- StateId INT PRIMARY KEY
- VehicleId INT
- ValidFromDate DATETIME

For each VehicleId there can be several rows in this table. I want to select the currently valid states (with a date parameter). There can be at most one valid state for each vehicle (the one with the highest ValidFromDate less than the date parameter).

If the table looks like this:

StateId  VehicleId  ValidFromDate
---      ---        ---
1        1          2009-01-01
2        1          2009-06-02
3        1          2009-11-03
4        2          2009-06-04
5        3          2009-10-05

And @Date = '2009-08-14' I want the query to produce the following result:

StateId  VehicleId  ValidFromDate
---      ---        ---
2        1          2009-06-02
4        2          2009-06-04

I think that the following query produces the correct result:

SELECT * FROM State s1
WHERE s1.StateId IN (SELECT TOP 1 StateId 
                     FROM State s2
                     WHERE s2.ValidFromDate <= @Date AND s1.VehicleId = s2.VehicleId 
                     ORDER BY s2.ValidFromDate DESC, s2.StateId DESC)

But there must be a better way to do this, using some kind of grouping clause, right? It doesn't feel right to use a subquery of the same table.

Answer 1

Your way with the TOP 1 will only return one row, not all the possible rows. I believe the result you're looking for is something like this.

It does however still use a subquery to get the MAX date. I do now know another way. However, I use this a lot and whilst it's not fast, I haven't found it to ever be too slow.

SELECT
 *
FROM
 State
WHERE
 State.ValidFromDate =
(
 SELECT
  MAX(ValidDromDate)
 FROM
  State2
 WHERE
  State2.VehicleId = State.VehicleId
 AND
  State2.ValidFromDate <= @Date
)

Answer 2

You can use the SQL 2005 RANK clause to help solve the "Give me the Top X by Group" problem.

A query like this would solve your problem. Note: I think the VehicleId should be 2 in your expected result set above given your data.

SELECT dateranks.*
FROM
(
    select
    s1.StateId,
    s1.VehicleId,
    s1.ValidFromDate,
    RANK() OVER (PARTITION BY s1.VehicleId ORDER BY s1.ValidFromDate desc) AS 'DateRank'
    FROM [state] s1
    WHERE
    ValidFromDate <= '8/14/2009'
)
dateranks
where
    dateranks.ValidFromDate <= '8/14/2009'
    AND DateRank = 1

Helpful links:

• More on the RANK keyword
• A better article explaining how to use RANK.