Display record with max date

Question

Display record with max date

shipra20

Valued Member

Points: 67
More actions
July 20, 2009 at 10:18 am

#95876

I have query like
select ID,name,code,date from tbl1.
In this each id can have numerous records with diffrent name,code and date.
I want to diaplay the record only with max date.How can do this.
ID--name--code--date
1---abc---A1----01/01/2009
1---xbc---A2----02/01/2009
2---xyz---A3----03/01/2009
1---ayz---A4----04/01/2009
the result should be
ID--name--code--date
1---xbc---A2----02/01/2009
1---ayz---A4----04/01/2009
Thank you
sh

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

shipra20 (7/20/2009)
I have query like
select ID,name,code,date from tbl1.
In this each id can have numerous records with diffrent name,code and date.
I want to diaplay the record only with max date.How can do this.
ID--name--code--date
1---abc---A1----01/01/2009
1---xbc---A2----02/01/2009
2---xyz---A3----03/01/2009
1---ayz---A4----04/01/2009
the result should be
ID--name--code--date
1---xbc---A2----02/01/2009
1---ayz---A4----04/01/2009
Thank you
sh

Sorry, not sure how you get from the initial data to the final data. Can you explain why the two rows that are shown were selected?

shipra20 Valued Member Points: 67 More actions · Answer 2

sorry for the typo.

Given:-

ID--name--code--date

1---abc---A1----01/01/2009

1---xbc---A2----02/01/2009

2---xyz---A3----03/01/2009

2---ayz---A4----04/01/2009

the result should be

ID--name--code--date

1---xbc---A2----02/01/2009

2---ayz---A4----04/01/2009

Thanks

--sh

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

shipra20 (7/20/2009)
sorry for the typo.
Given:-
ID--name--code--date
1---abc---A1----01/01/2009
1---xbc---A2----02/01/2009
2---xyz---A3----03/01/2009
2---ayz---A4----04/01/2009
the result should be
ID--name--code--date
1---xbc---A2----02/01/2009
2---ayz---A4----04/01/2009
Thanks
--sh

Okay, makes more sense now. Now I'd like to see what you have tried so far to solve your problem. In the mean time, I will put something together.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Here is the code I came up with. Too bad I don't know what you have tried so far to solve your problem. It would have helped to see where you were having problems.