How can I do a "Grouped TOP 1"?

[amilcar]

I dont know how to do this, given this table:

-----------------------------------
CaseId | User  | Timestamp
-----------------------------------
   1   |   1   | 01/26/2011
   2   |   3   | 03/12/2011
   3   |   2   | 03/20/2011
   4   |   1   | 04/16/2011
   5   |   3   | 05/17/2011
   6   |   1   | 05/06/2011
   7   |   1   | 08/18/2011

What I need to do is a query that returns only the latest case id of every user field, for example:

-----------------------------------
CaseId | User  | Timestamp
-----------------------------------
   3   |   2   | 03/20/2011
   5   |   3   | 05/17/2011
   7   |   1   | 08/18/2011

How can i do this ??

thanks i cant find the way to do it.

[NeoPa]

Check out SELECT Record From Group With Max Value. It may be in the Access forum but the concept is the same for SQL anywhere I think.

[amilcar]

Thanks! I resolve it this way:

I Have 2 tables, users, and cases, what I do is to call the users table with a subquery (inner query) to return only the latest case of that user (order by date)

Something like this (simplified):

SELECT dbo.users.id, [B][U](SELECT TOP (1) CaseId FROM dbo.cases WHERE (dbo.cases.user = dbo.users.id) ORDER BY timestamp DESC) AS case[/U][/B] FROM         dbo.users

[NeoPa]

Looks good to me (Easier to read if shown a little more vertically - but fundamentally makes sense) :-)

[ck9663]

Be careful in using your code in big tables. Looks like you're doing an RBAR processing. It can have effects on performance.

Happy Coding!!!

~~ CK