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 );

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: