CODEWITHSUNDEEP
sql-server
user1211398
user1211398

Reputation: 113

sql select from multiple records only the most recent

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...

Upvotes: 9

Views: 30359

Answers (4)

Umair
Umair

Reputation: 3243

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

Upvotes: 2

Diego
Diego

Reputation: 18349

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

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 837946

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
)

Upvotes: 16

kosa
kosa

Reputation: 66637

Assuming first record means highest ID, you may try your query with descending orderby ID and TOP n.

Upvotes: 1

Related Questions