Q

Query for five most recent dates

I have a table that I want to query by a char date field. I want to get the five most recent dates. So far using MAX in a subquery I can get one row. How do I get the rest?

I have a table that I want to query by a char date field. I want to get the five most recent dates. So far using MAX in a subquery I can get one row. How do I get the rest? Here is an example:
Select ky_ba,......,dt_prcs
from my_table
where ky_ba = 999999999 and dt_prcs =
   ( Select MAX(dt_prcs) from my_table where ky_ba = 999999999 );

I see where you say you have a char date field. In my example, I will use a date date field. I'd suggest you change your schema to do the same. If you can't, wrap a to_date(dt_prcs, ' ') around it.

First, what happens if you have six that have an equal max date? Just return five randomly? Return all? I will guess you want to always return only five and you have no additional criteria to rank them. The easiest way to do this is with the row_number() analytic function.

Here is a complete example:

create table my_table (
  ky_ba number,
  some_data varchar2(10),
  some_more_data number,
  dt_prcs date );
  
insert into my_table values (999999999, 'aaa', 9999, to_date( '01-jan-2005', 'DD-MON-YYYY') );
insert into my_table values (999999999, 'bbb', 8888, to_date( '01-feb-2005', 'DD-MON-YYYY') );
insert into my_table values (999999999, 'ccc', 7777, to_date( '01-mar-2005', 'DD-MON-YYYY') );
insert into my_table values (999999999, 'ddd', 6666, to_date( '01-apr-2005', 'DD-MON-YYYY') );
insert into my_table values (999999999, 'eee', 5555, to_date( '01-may-2005', 'DD-MON-YYYY') );
insert into my_table values (999999999, 'fff', 4444, to_date( '01-jun-2005', 'DD-MON-YYYY') );
insert into my_table values (999999999, 'ggg', 3333, to_date( '01-jul-2005', 'DD-MON-YYYY') );
insert into my_table values (999999999, 'hhh', 2222, to_date( '01-aug-2005', 'DD-MON-YYYY') );
insert into my_table values (999999999, 'iii', 1111, to_date( '01-sep-2005', 'DD-MON-YYYY') );
insert into my_table values (888888888, 'jjj', 0000, to_date( '01-oct-2005', 'DD-MON-YYYY') );

commit;


select ky_ba, some_data, some_more_data, dt_prcs
  from (   
    Select ky_ba, some_data, some_more_data, dt_prcs,
           row_number() over(partition by ky_ba order by dt_prcs desc) rn
      from my_table )
  where ky_ba = 999999999   
    and rn <= 5;
Row_number() is a great way to return the top(n) or bottom(n) values. For further reading, check out the rank() and dense_rank() functions in the documentation.
This was first published in December 2005

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close