Re: Can I use an index to get the last n records?

On Jul 16, 11:00 am, Timasmith <timasm..._at_hotmail.com> wrote:
> I want to execute something like this (which doesnt work at all)
>
> select * from sales
> where sales_person_id = :sales_person_param
> and rownum < 10
> order by sales_date_time desc

Oracle "assigns" the psuedo-column ROWNUM before any ordering is done. However you can wrap the ordered query in a subquery, then apply
the ROWNUM filter afterward:

select * from (
  select * from sales
  where sales_person_id = :sales_person_param   order by sales_date_time desc
)
where rownum <= 10

Note that if you display the ROWNUM as part of the results, any "ties" will be incorrectly assigned different ranks. To handle that, use the RANK() analytic in the subquery, which will handle ties appropriately. Probably not a big deal for this particular case thought.

[On an un-related note, some consider "SELECT *" to be ill-advised practice from within an appliction, because it breaks when columns are added, dropped or renamed, plus it relies on an (unreliable) ordering of columns returned]

HTH,
John Hinsdale Received on Mon Jul 16 2007 - 11:07:33 CDT