Hi,
I have the following table:
TableName: WC
Columns:
UserID PeriodOfEntry LastName
1 06/02/2010 Apple
1 04/01/2009 Apple
2 04/12/2000 Peach
2 04/11/2000 Peach
2 06/02/2010 Peach
3 01/11/2000 Grape

I'm trying to get it so the query will return a unique UserID with
it's latest PeriodOfEntry only, I've tried this:
select UserID, PeriodOfEntry
from WC
where UserID in
(
select UserID
from WC
group by UserID
)
group by UserID, PeriodOfEntry
order by UserID, PeriodOfEntry

but this just ordered them, I was not able to get just one record per
user. I wanted some results like this:
UserID PeriodOfEntry LastName
1 06/02/2010 Apple
2 06/02/2010 Peach
3 01/11/2000 Grape

just the 1 record with lasted PeriodOfEntry, is there a way to do this
in a single query?

Or do I need to maybe load the data into a temp table, putting just
the unique IDs into a temptable, and use a cursor aganist the
temptable and pick up each ID and run another query aganist the table
looking up based on the ID and ordering by latest date.

Thank you.

soni kundani (soni.kundani (AT) gmail (DOT) com) writes:
WITH numbered AS (
SELECT UserID, PeriodOfEntry, LastName,
rowno = row_number() OVER(PARTITION BY UserID
ORDER BY PeriofOfEntry DESC)
FROM tbl
)
SELECT UserId, PeriodOfEntry, LastName
FROM tbl
WHERE rowno = 1

This solution requires SQL 2005 or alter. (Please always include which
version of SQL Server you use.)

The part that starts with WITH is a Common Table Expression, which
can be described as a temporary view, visible only for the query.
In the CTE we determine a row number which we then filter on.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

If you need only UserID and PeriodOfEntry, then try this (else use
Erland's solution):

SELECT UserID, MAX(PeriodOfEntry)
FROM WC
GROUP BY UserID;

--
Plamen Ratchev
http://www.SQLStudio.com

Below worked, thank you so much! I was using SQL 2005, so the WITH
statement worked, thanks again!