How do I Query just latest x records of each item in a table? 3

[ljsmith91]

I am not sure if this is possible but wanted to ask the sql experts. I need to pull just the X latest records for each item from a database table. A small sample of the table is below. So, if I just want to pull the 2 most recently dated items/schedules in a query, I am not sure how I would accomplish this. I really need to pull the latest 10 but I wanted to simplify for this question. So, I would need the records with * at the end to get pulled if I wanted latest 2 records of each item/schedule. Can anyone help direct me to how to go about something like this? Thanks in advance.

table_items

item schedule date

a as1 2011-02-10
a as1 2011-04-11 *
a as1 2011-08-12 *
a as2 2011-01-10
a as2 2011-04-10 *
a as2 2011-07-10 *
b bs1 2009-02-10
b bs1 2010-04-11 *
b bs1 2011-08-12 *
b bs2 2008-01-10
b bs2 2009-04-10 *
b bs2 2006-07-10 *

 

[Turkbear]

Hi,
try this:

Select a.* from
(select item,schedule,date from table_items order by date DESC)
where
rownum < 11;

That will give you the 10 most recent ( as long as no 2 have the same date, if they do , increase the number) - alter the rownum quantity to change how many are returned.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Since I believe that LJ wants the "latest 2 records of each item/schedule", he can use one of Oracle's remarkably feature-rich Analytic Functions:

select * from table_items;

ITEM SCHEDULE  DT
---- --------- ---------
a    as1       10-FEB-11
a    as1       11-APR-11
a    as1       12-AUG-11
a    as2       10-JAN-11
a    as2       10-APR-11
a    as2       10-JUL-11
b    bs1       10-FEB-09
b    bs1       11-APR-10
b    bs1       12-AUG-11
b    bs2       10-JAN-08
b    bs2       10-APR-09
b    bs2       10-JUL-06

12 rows selected.

select *
  from (select item
              ,schedule
              ,dt
              ,rank() over (partition by item, schedule order by dt desc) rnk from table_items)
 where rnk <= 2;

ITEM SCHEDULE  DT                     RNK
---- --------- --------------- ----------
a    as1       12-AUG-11                1
a    as1       11-APR-11                2
a    as2       10-JUL-11                1
a    as2       10-APR-11                2
b    bs1       12-AUG-11                1
b    bs1       11-APR-10                2
b    bs2       10-APR-09                1
b    bs2       10-JAN-08                2

8 rows selected.

Please look over this code...analyze what it does...experiment with it, then let us know if it resolves your need and if you have any questions.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ljsmith91]

Mufasa,

Yes...thanks...it is working. It does the trick for me. I appreciate the useful information.

 

[Turkbear]

Hi,
I appreciate the star but SantaMufasa really deserves one for the more Oracle specific ( and more modern) method so I gave him one.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ljsmith91]

I just realized based on the query output that there are some items that have the same date. What I need is DISTINCT date and a MAX of the field Status which would tell me the highest exit code for that date. I would want to create 10 records for each item with but each of the 5 records must specify the MAX Status for each of the 5 different and latest dates. I have read up on the RANK and attempted several times to produce the desired result but without success. I think because the rnk field is output in the query and makes eac record unique. Can this be done with rank? My input table looks more like this sample for 1 of the items and * are records I would like to have outputted assuming 5 records per item, schedule:

table_items

item schedule date status

a as1 2011-02-10 0
a as1 2011-02-10 5 *
a as1 2011-02-10 4
a as1 2011-01-10 0 *
a as1 2011-04-10 0 *
a as1 2011-07-10 4 *
a as1 2011-06-10 0 *

 

[Dagon]

I'm not entirely clear what your requirements are. I'm not sure where 10 comes in because you seem to want 5 distinct date records.

However, I shall answer the following problem, which is presumably close to what you want. You want the 5 highest dates and the highest status within each of those dates. You just need to enclose the rank within a select..group by. Also, it would be better to use dense_rank rather than rank to ensure that you get exactly 5 dates when there are lots of dates of the same value.

drop table daterank;

create table daterank (item varchar2(10), schedule varchar2(20), sdate date, status number);

insert into daterank values ('a', 'as1', to_date('2011-07-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-06-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-04-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-02-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-02-10', 'YYYY-MM-DD'), 5);
insert into daterank values ('a', 'as1', to_date('2011-02-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-01-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2010-12-10', 'YYYY-MM-DD'), 5);
insert into daterank values ('a', 'as1', to_date('2010-11-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('b', 'as1', to_date('2010-12-10', 'YYYY-MM-DD'), 6);
insert into daterank values ('b', 'as1', to_date('2010-12-10', 'YYYY-MM-DD'), 3);
insert into daterank values ('b', 'as1', to_date('2010-12-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('b', 'as1', to_date('2010-11-10', 'YYYY-MM-DD'), 2);
insert into daterank values ('b', 'as1', to_date('2010-10-10', 'YYYY-MM-DD'), 0);

select * from daterank order by item, schedule, sdate;

select item, schedule, sdate, max(status)
from
(select item, schedule, status, sdate, dense_rank() over (partition by item, schedule order by sdate desc) as rn
from daterank)
where rn<=5
group by item, schedule, sdate
order by item, schedule, sdate desc;

ITEM       SCHEDULE             SDATE     MAX(STATUS)
---------- -------------------- --------- -----------
a          as1                  10-JUL-11           4
a          as1                  10-JUN-11           0
a          as1                  10-APR-11           0
a          as1                  10-FEB-11           5
a          as1                  10-JAN-11           0
b          as1                  10-DEC-10           6
b          as1                  10-NOV-10           2
b          as1                  10-OCT-10           0

For Oracle-related work, contact me through Linked-In.

 

[ljsmith91]

Thanks Dagon,

It doesn't seem to be working. WHen I just execute the inner query with the dense rank, I see the list of unique items/schedules and in my case it ranks a specific items/schedule with 150+ records (ranked1-150) some of the same date and some of different dates. The first 6 records of a specific item/schedule has the same date and is ranked 1-6 and a different date 7-11 etc and when the outer query runs against it, I end up with only 1 record for that item because of max status off only the first <= 5 ranked records. No other dates are included because of the Inner query ranking and <= 5 on the outer query. Should MAX(status) be in the INNER Query? I keep trying that approach but still can't get the end result of 5 different dated records per item, schedule with MAX status on each unique date.

Thanks for any additional guidance on this.

ljs

 

[Dagon]

Did you use dense_rank instead of rank as I suggested? As the following example shows, my query should cope with the situation you described:

drop table daterank;

create table daterank (item varchar2(10), schedule varchar2(20), sdate date, status number);

insert into daterank values ('a', 'as1', to_date('2011-07-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-07-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-07-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-07-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-07-10', 'YYYY-MM-DD'), 5);
insert into daterank values ('a', 'as1', to_date('2011-07-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-06-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-06-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-06-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-06-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-06-10', 'YYYY-MM-DD'), 5);
insert into daterank values ('a', 'as1', to_date('2011-06-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-05-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-05-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-05-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-05-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-05-10', 'YYYY-MM-DD'), 5);
insert into daterank values ('a', 'as1', to_date('2011-05-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-04-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-04-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-04-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-04-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-04-10', 'YYYY-MM-DD'), 5);
insert into daterank values ('a', 'as1', to_date('2011-04-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-03-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-03-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-03-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-03-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-03-10', 'YYYY-MM-DD'), 5);
insert into daterank values ('a', 'as1', to_date('2011-03-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-02-10', 'YYYY-MM-DD'), 4);
insert into daterank values ('a', 'as1', to_date('2011-02-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-02-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-02-10', 'YYYY-MM-DD'), 0);
insert into daterank values ('a', 'as1', to_date('2011-02-10', 'YYYY-MM-DD'), 5);
insert into daterank values ('a', 'as1', to_date('2011-02-10', 'YYYY-MM-DD'), 4);

select distinct item, schedule, sdate from daterank order by sdate desc; 

ITEM       SCHEDULE             SDATE    
---------- -------------------- ---------
a          as1                  10-JUL-11
a          as1                  10-JUN-11
a          as1                  10-MAY-11
a          as1                  10-APR-11
a          as1                  10-MAR-11
a          as1                  10-FEB-11

select item, schedule, sdate, max(status)
from
(select item, schedule, status, sdate, dense_rank() over (partition by item, schedule order by sdate desc) as rn
from daterank)
where rn<=5
group by item, schedule, sdate
order by item, schedule, sdate desc;

ITEM       SCHEDULE             SDATE     MAX(STATUS)
---------- -------------------- --------- -----------
a          as1                  10-JUL-11           5
a          as1                  10-JUN-11           5
a          as1                  10-MAY-11           5
a          as1                  10-APR-11           5
a          as1                  10-MAR-11           5

For Oracle-related work, contact me through Linked-In.

 

[ljsmith91]

Hi Dagon,

Yes...I was using dense_rank. However, I neglected to state in my sample that my date field was actually a date and unique time. I was specifying to_char(My_DATE, 'yyyy-mm-dd') in SELECT Statement but not in the dense_rank ORDER BY statement(I was just stating My_Date). When I changed to match how I coded my SELECT Statement, it worked.

Thanks so much for the valuable replies.

ljs