LIMIT equivalent 1

[TGJ]

I am wondering if there is an oracle equivalent to the mysql LIMIT function.

This is what I am trying to achieve:

SELECT *
FROM data
WHERE disabled_date > last_day_empl_paid_date
ORDER BY sequence_num DESC
Limit 1

 

[SantaMufasa]

TGJ,

Are you simply looking to see all data on the row with the highest-valued "sequence_num" whose disabled_date > last_day_empl_paid_date? If so, then there are multiple methods to achieve this:

Sample data:

select * from data;

DISABLED_ LAST_DAY_ SEQUENCE_NUM
--------- --------- ------------
12-AUG-05 10-AUG-05           17
12-AUG-05 10-AUG-05           16
12-AUG-05 04-AUG-05           31
13-AUG-05 14-AUG-05           55

Method 1:

select * from data
where sequence_Num in (select max(sequence_num) from data
                        where disabled_date > last_day_empl_paid_date)
  and disabled_date > last_day_empl_paid_date;

DISABLED_ LAST_DAY_ SEQUENCE_NUM
--------- --------- ------------
12-AUG-05 04-AUG-05           31

Method 2:

select *
from (select * from data
       where disabled_date > last_day_empl_paid_date
       ORDER BY sequence_num DESC)
where rownum = 1;

DISABLED_ LAST_DAY_ SEQUENCE_NUM
--------- --------- ------------
12-AUG-05 04-AUG-05           31

I like the second method since if you want to see the top two rows, then you can easily adjust the code to make that happen:

select *
from (select * from data
       where disabled_date > last_day_empl_paid_date
       ORDER BY sequence_num DESC)
where rownum <= 2;

DISABLED_ LAST_DAY_ SEQUENCE_NUM
--------- --------- ------------
12-AUG-05 04-AUG-05           31
12-AUG-05 10-AUG-05           17

Let us know if you like either one of these options.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[TGJ]

Method 1 is perfect, thanx =)