Prev: reThink Migration – It’s Time for Change!
Next: Strip the first byte from an image column.
From: soni kundani on 2 Jun 2010 16:31
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.

From: Erland Sommarskog on 2 Jun 2010 17:34
soni kundani (soni.kundani(a)gmail.com) writes:
> 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,

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: Plamen Ratchev on 2 Jun 2010 21:46
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
From: soni kundani on 3 Jun 2010 09:07
Below worked, thank you so much! I was using SQL 2005, so the WITH
statement worked, thanks again!

>
> 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, esq...(a)sommarskog.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/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: reThink Migration – It’s Time for Change!
Next: Strip the first byte from an image column.