CODEWITHSUNDEEP
sqlnetezza
jasonlcy91
jasonlcy91

Reputation: 464

SQL get last 90 days records of an id, with each id having different last day

I have a "Daily usage" table that goes like this:

ID, Date, Usage
1, 2018-07-25, 30
1, 2018-07-23, 30
1, 2018-07-22, 30
1, 2018-07-21, 30
1, 2018-07-20, 30
2, 2018-07-29, 30
2, 2018-07-28, 30
2, 2018-07-27, 30
2, 2018-07-26, 30

How to get last 3 days records of an id, with each id having different last day, preferably in Netezza SQL, ie

ID, Date, Usage
1, 2018-07-25, 30
1, 2018-07-23, 30
2, 2018-07-29, 30
2, 2018-07-28, 30
2, 2018-07-27, 30

Note that for ID=1, only get 07-25 (day 3) and 07-23 (day 1), but not 07-22, as I want any available date in the range of 3 days from the last day

I think rank() or row_number() probably won't fit the job

Upvotes: 1

Views: 195

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Use row_number():

select t.*
from (select t.*, row_number() over (partition by id order by date desc) as seqnum
      from t
     ) t
where seqnum <= 3;

EDIT:

If you want up to 2 days before the maximum, then use:

select t.*
from (select t.*, max(date) over (partition by id) as max_date
      from t
     ) t
where date >= duration_subtract(max_date, -2);

Upvotes: 3

Related Questions