sql select from multiple records only the most recent

Question

i have a table named customer_age that loks like this:

ID     1   2    3     4     5    6     7     8     9

NAME  JIM  JIM  JIM  NICK  NICK  NICK  Paul  Paul  Paul                                       
VALUE  20  13   12    10    20    8     4     24    14  

and i want to display only the first record from each name. Something like this

ID     1     4      7    

NAME  JIM  NICK   Paul                                  
VALUE  20   10      4    

So far i have not been able to work it out. i use sql server 2005 Any help would be appreciated...

Answer 1

Try using a subselect to find the lowest ID for each name, and use that set of IDs to pull the records from the main table:

SELECT ID, Name, Value
FROM customer_age
WHERE ID IN
(
    SELECT MIN(ID) AS ID
    FROM customer_age
    GROUP BY Name
)

Answer 2

Just select the first record for each name using cross apply:

SELECT 
ca.ID, ca.NAME, ca.VALUE
FROM customer_age c
CROSS APPLY (SELECT TOP 1 ID, NAME, VALUE 
       FROM customer_age ca 
       WHERE ca.NAME = c.NAME ORDER BY ID) ca
ORDER BY ca.ID

Answer 3

How about using window functions??

SELECT Id, Name, Value
FROM (
    SELECT Id, Name, Value, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Id ASC) AS rowNum
    FROM customer_age
) AS sub
WHERE rowNum = 1