TOP function and Partitioning

UDA
Enthusiast

TOP function and Partitioning

When I run the following I get:

SELECT distinct week_id
FROM Tbl_A
WHERE region IN ('GNI', 'GED');

week_id
-----------
200734
200735
200738
200739

I want only to retrieve the last two weeks i.e. 200738 & 200739.
This data is always moving and I only ever want the last 2 weeks.

I have tried the following but cannot get them to work:
SELECT distinct top 2 week_id FROM Tbl_A;
-- top does not work with distinct.

and

SELECT distinct week_id, ROW_NUMBER() OVER (PARTITION BY week_id ORDER BY week_id)
FROM Tbl_A
WHERE region IN ('GNI', 'GED');
-- this will return every row with week_id and apply a running sequence. I thought it might actually
-- partition the data!

Any ideas? I'm sure I'm missing something blatently obvious.

2 REPLIES
Enthusiast

Re: TOP function and Partitioning

Please ignore thread.
I found as follows:

select top 2 week_id
from (select distinct week_id from incptanal_ged WHERE region IN ('GNI', 'GED')) a
order by week_id desc;

Re: TOP function and Partitioning


select week_id from incptanal_ged WHERE region IN ('GNI', 'GED')

qualify row_number() over(partition by 1order by week_id desc)<=2