How can I pull first line of each group?

Question

Post reply

How can I pull first line of each group?

GivThx

SSCommitted

Points: 1758
More actions
May 27, 2004 at 3:29 pm

#108319

Hi, All
Here is my dilema..
I want to pull each ID for lastest trans_date.
Data:
ID         TRANS_DATE        AMOUNT
1             10/12/2002          500.00
2             07/08/2001          250.00
3              08/25/2003         300.00
2             11/10/2002          450.00
1             01/26/2003           100.00
2             03/16/2002           350.00

Any Idea? No Cursor Please!.. When I deal with 10 mil records...No Cursor...
Thx
Jay

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Try

SELECT

N.[ID], N.TRANS_DATE, N.AMOUNT

FROM

tblName N

INNER JOIN

(SELECT [ID], MAX(TRANS_DATE) TT FROM tblName T2 GROUP BY [ID]) TQ

ON

N.[ID] = TQ.[ID] AND

N.TRANS_DATE = TQ.TRANS_DATE

GivThx SSCommitted Points: 1758 More actions · Answer 2

GivThx

SSCommitted

Points: 1758

May 27, 2004 at 3:58 pm

#508087

Thx..

Jay

KarstenEnH SSC-Addicted Points: 444 More actions · Answer 3

Hi Jay,

I often heard inner join is to be preferred always. But there is another way to solve your problem using a simple where clause. This is easyer to write down and estimatet execution plan shows no difference in performance. I would like to know if you get the same result using this query:

select * from tablename a where a.trans_date =

(select max (trans_date) from tablename where id = a.id)

Karsten