Subqueries / join on the same table

join, sql, sql-server, subquery

Solution

Try this:

WITH [ranked] AS (
     SELECT VIN, Record, RANK() OVER(PARTITION BY VIN ORDER BY DateChange DESC, newid()) [rank]
     FROM tbl_veh)

SELECT VIN, Record
FROM [ranked]
WHERE [rank] = 1;

or "less complex" version (without using `CTE`):

SELECT VIN, Record
FROM (
    SELECT VIN, Record, RANK() OVER(PARTITION BY VIN ORDER BY DateChange DESC, newid()) [rank]
    FROM tbl_veh) as [ranked] 
WHERE [rank] = 1;

Problem

I have a table that looks like the following Table `tbl_veh` ``` VIN Record DateChange 11223344 123A 6/24/2012 11223344 121G 7/20/2013 11223344 2D54 2/24/2013 55445588 44D4 2/27/2012 55445588 855D 3/15/2013 ``` So I would like to select the VIN and record but only for the most recent date. How would I do that? So I would get back ``` 11223344 and 121G 55445588 and 855D ```

Original source