How is his done

Hi,
I have the foll table
col1 col2
1 2002-01-01
1 2003-01-01
1 2004-01-01
2 2004-01-01
1 2005-01-01
how can I display the most recent 2 or 3 of id 1?
ThanksCreate a ranking expression for col2 based on a partition of col1 and
generate the subset. To put it in perspective:
SELECT *
FROM tbl t1
WHERE ( SELECT COUNT(*) FROM tbl t2
WHERE t1.col1 = t2.col1
AND t1.col2 <= t2.col2 ) <= 2 ;
Essentially the subquery in the WHERE clause is the ranking expression that
generates the quota. In SQL 2005, you can use RANK() function like:
SELECT *
FROM ( SELECT col1, col2,
RANK() OVER ( PARTITION BY col1
ORDER BY col2 ) AS "rank"
FROM tbl ) D
WHERE rank <= 2 ;
Anith|||For SQL 2000:
(If you want the most recent 2)
SELECT TOP 2
Col1
, Col2
FROM MyTable
WHERE Col1 = 1
ORDER BY Col2 DESC
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message news:u6vfA0umGHA.4700@.TK2MSFTNGP0
3.phx.gbl...
> Create a ranking expression for col2 based on a partition of col1 and
> generate the subset. To put it in perspective:
>
> SELECT *
> FROM tbl t1
> WHERE ( SELECT COUNT(*) FROM tbl t2
> WHERE t1.col1 = t2.col1
> AND t1.col2 <= t2.col2 ) <= 2 ;
>
> Essentially the subquery in the WHERE clause is the ranking expression tha
t
> generates the quota. In SQL 2005, you can use RANK() function like:
>
> SELECT *
> FROM ( SELECT col1, col2,
> RANK() OVER ( PARTITION BY col1
> ORDER BY col2 ) AS "rank"
> FROM tbl ) D
> WHERE rank <= 2 ;
>
> --
> Anith
>
>|||Hi,
Your solution seems to work excellent. Actually I can then adjust it on the
fly to return the specified amount by changing the <= 2 at the end, however,
there is a slight performance hit on a hugh table. I guess that's because of
the count(*). I tested it on a 500000 row table.
Thanks
"Anith Sen" wrote:

> Create a ranking expression for col2 based on a partition of col1 and
> generate the subset. To put it in perspective:
> SELECT *
> FROM tbl t1
> WHERE ( SELECT COUNT(*) FROM tbl t2
> WHERE t1.col1 = t2.col1
> AND t1.col2 <= t2.col2 ) <= 2 ;
> Essentially the subquery in the WHERE clause is the ranking expression tha
t
> generates the quota. In SQL 2005, you can use RANK() function like:
> SELECT *
> FROM ( SELECT col1, col2,
> RANK() OVER ( PARTITION BY col1
> ORDER BY col2 ) AS "rank"
> FROM tbl ) D
> WHERE rank <= 2 ;
> --
> Anith
>
>|||Hi,
Your solution seems to work excellent. Actually I can then adjust it on the
fly to return the specified amount by changing the <= 2 at the end, however,
there is a slight performance hit on a hugh table. I guess that's because of
the count(*). I tested it on a 500000 row table.
Thanks
"Anith Sen" wrote:

> Create a ranking expression for col2 based on a partition of col1 and
> generate the subset. To put it in perspective:
> SELECT *
> FROM tbl t1
> WHERE ( SELECT COUNT(*) FROM tbl t2
> WHERE t1.col1 = t2.col1
> AND t1.col2 <= t2.col2 ) <= 2 ;
> Essentially the subquery in the WHERE clause is the ranking expression tha
t
> generates the quota. In SQL 2005, you can use RANK() function like:
> SELECT *
> FROM ( SELECT col1, col2,
> RANK() OVER ( PARTITION BY col1
> ORDER BY col2 ) AS "rank"
> FROM tbl ) D
> WHERE rank <= 2 ;
> --
> Anith
>
>