Sql select from only a few records

Question

I have a table called customer_age that looks 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 that

 ID 1 4 7 NAME JIM NICK Paul VALUE 20 10 4 

So far, I have not been able to handle this. I am using SQL Server 2005 Any help would be appreciated ...

Answer 1

Try using a subquery to find the lowest identifier for each name and use this set of identifiers to pull 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

How to use 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 

Answer 3

Assuming the first record means the highest identifier, you can try your query with decreasing order of ID and TOP n.

Answer 4

Just select the first entry 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