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 last published in December 2005

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close