SQL - selecting the lastest record of each group

[Guest_imported]

I have a table with the following records:

AppNo EventDate EventId
----- ------------------------ --------
1 2002-09-18 15:03:11.933 1
1 2002-09-18 16:03:11.953 2
2 2002-09-18 15:03:55.447 3
2 2002-09-19 10:03:55.457 4 *
3 2002-09-18 15:05:04.477 5
3 2002-09-18 16:05:04.487 6 *
1 2002-09-19 15:06:49.297 7 *

How do i use SQL statement to select the latest record for each AppNo (ie select the latest record for AppNo = 1, latest record for AppNo = 2, and so on)?

The result should look like the following (record marked * above):

AppNo EventDate EventId
----- ------------------------ --------
1 2002-09-19 15:06:49.297 7
2 2002-09-19 10:03:55.457 4
3 2002-09-18 16:05:04.487 6

I'm using SQL Server 7.0 on Windows NT Server 4.0.
I urgently require the answer.

Thanks a lot.

 

[Guest_imported]

i have found the answer:

select * from thetable xx
where EventId = ( select TOP 1 EventId
from thetable
where AppNo= xx.AppNo
order by EventId desc )

 

[angiole]

Since EventID is a floow along piece of info for EventDate, grouped by AppNo, the following will work as well

Select   AppNo,
         substr(max(EventDate || EventID),1,23) Eve_Date,
         substr(max(EventDate || EventID),24,1) Eve_ID
From     TheTable
Where    ...
Group by AppNo

I am assuming a text character type for your event Date as it apppears to be a log of some kind. As well your event ids might be larger, but adjust accordingly.

The || is the Oracle concatenation operator, use whatever you are allowed.

Cheers
AA 8~)

 

[Guest_imported]

thanks a lot. the answer i found can do the trick already.