How do I return a sorted top ten list? 2

[Palooka]

Hi

What I want to do is something similar to this:

  select * from employee
  where rownum < 10
  order by startdate desc

However it seems that

rownum

supersedes the

order by

part of the query. What I want, is to have ALL the records sorted first, and then present the "top ten" of those.

(In MS SQL Server there is a function called TOP, but Oracle does not have one, as far as I know.)

Can anyone help me solve this problem?

Thanks in advance,
Palooka

 

[carp]

You can't do this in SQL prior to Oracle 8. Instead, you'll have to use PL/SQL, create a cursor that's populated by
select * from employee
order by startdate desc;
and just pick off ther first ten records.

 

[Felgar]

I have not yet tried this, as my Oracle server is in the midst of being reinstalled and is currently not available for me to use. However, I am fairly sure this will work.

select *
from employee E1
where 10 <
(select count(*)
from employee E2
where E2.startdate > E1.startdate)
order by E1.startdate desc

In other words, for each employee, the query checks how many others have startdates after them. If there are less than 10 you get the row. Of course, this query is terribly innefficient, as you scan the table n^2 times for n records in the table. But if you have indexes on the dates, and your table is small, and you MUST use a query, I think this should get the job done. Sorry I could not test it to make sure.

 

[carp]

Yep - that should do it! Outstanding, Felgar!

 

[Guest_imported]

Another way to do this is to use the Oracle pseudocolumn ROWNUM. The following example retrieves the top 5 rows. Note that the subselect does the ordering, then the outer select returns the top 5 rows from the sorted list.

select * from (select * from Employee order by last_name) where ROWNUM <= 5

 

[Palooka]

A good idea, but I'm afraid it doesn't work here, since we are using Oracle7 Server Release 7.3.4.0.1. This particular database is the foundation of the Agresso application, and upgrading it is out of the question, unfortunately.

In older versions of Oracle, subqueries cannot contain 'order by' (or 'for update of') clauses.

I have also tried to create a view containing an order by, but that isn't allowed either.

Thanks,
Palooka

 

[saklay]

Palooka & I have similar problems. I'm currently using Oracle 8i but the &quot;order by&quot; clause still doesn't work. I've read in another thread that the &quot;order by&quot; clause should work in versions 8i and newer. Any comments?

Thanks.

saklay

 

[yaffle]

You could try something along the lines of:[tt]
select e.*
from employee e,
(select e1.emp_id, count(e2.emp_id)
from employee e1, employee e2
where e1.startdate < e2.startdate (+)
group by e1.emp_id
having count(e2.emp_id) < 10) sub
where e.time_key = sub.time_key
order by e.startdate desc[/tt]
Doing it in PL/SQL would be quicker though, especially as the row count in employee increases.