All Forums UDA
marcmc 112 posts Joined 12/05
03 Oct 2007
TOP function and Partitioning

When I run the following I get:SELECT distinct week_idFROM Tbl_AWHERE region IN ('GNI', 'GED');week_id ----------- 200734200735200738200739I 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.andSELECT distinct week_id, ROW_NUMBER() OVER (PARTITION BY week_id ORDER BY week_id)FROM Tbl_AWHERE 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.

marcmc 112 posts Joined 12/05
03 Oct 2007

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;

satyapal240 2 posts Joined 07/06
14 Dec 2007

select week_id from incptanal_ged WHERE region IN ('GNI', 'GED') qualify row_number() over(partition by 1order by week_id desc)<=2

You must sign in to leave a comment.