Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
n_hancock
Starting Member
1 Post |
 Posted - 2004-05-24 : 21:06:12
|
| I have a table with the primary key OfferID and Sequence_number.The other fields involved are status, and create_date.I want to build a SELECT statement to return IDs where, for each OfferID, the status of record max(sequence_number) is 'R', AND the status is 'R' of the record before as well, ie (max(sequence_number)-1). Eventually this will report on dates within a certain range, but I need to get the SELECT working first.Any ideas?Nigel Hancock |
|
|
srinivasanr
Starting Member
15 Posts |
Posted - 2004-05-25 : 02:48:50
|
| Nigel,Hope this snippet will work out...SELECT a.OfferID, a.sequence_number FROM offer aWHERE a.status = 'R'GROUP BY a.OfferID, a.sequence_number HAVING a.sequence_number IN (SELECT TOP 2 b.sequence_number FROM offer b WHERE a.OfferId = b.OfferID ORDER BY b.sequence_number DESC)Check OutWallops!!! |
 |
|
|
|
|
|
SELECTing the 2 most recent rows