I have a problem that probably has a very easy solution, but perhaps I'm too fried from 16 hours work days to figure it out.

I have a Status table that references support tickets. Every time something is done to a ticket, a new status record gets inserted with a foreign key of the ticket ID. Whenever I need to find out the latest status, I use a query that selects the top status like this:

SELECT Top 1 StatusID
FROM Status
WHERE TicketID_FK = [ticketid]
ORDER BY StatusID DESC

That's a no-brainer. However, now, I need to run a query to find ALL DISTINCT TicketID_FK's in Status, but when it selects a DISTINCT TicketID_FK, I want it to select the one with the highest StatusID.

For instance, if the following data exists in the table:

StatusID TicketID_FK
1 20
2 20
3 20
4 21
5 21
6 21
7 21

I want the query that would give me a recordset like this:

TicketID_FK = 20 StatusID = 3
TicketID_FK = 21 StatusID = 7

Can this be done with a SQL query, or will it have to be sorted some other way using code within the page?

Thank you in advance to anyone that can help.

Derrick