Explain the query

[phani1092]

    select e.employee_id from employees e where 
         (select count(*) from employees f where f.employee_id < e.employee_id) < 5;

The above query gives me first five employee id's. I want to know about the output produced by the sub query and explain. Thanks in advance.

[ryantroop]

select 
  e.employee_id --get the ids
from 
  employees e --from employees table aliased as e
where
 ( --begin sub query...
   select 
     count(*) --get the total number of rows
   from 
     employees f --from employees table aliased as f
   where 
     f.employee_id < e.employee_id -- where.. I believe this will return n-1 results where n is the total rows.
 ) < 5; -- where the values of the subquery are less than 5

Essentially, you're asking for all rows, but only give you the first 5. This is, I believe, a terrible query.. if I understand it right, it's hitting the same table 5 or 6 times for something that is much simpler to do as a straight up query... the only reason I can see doing this at all is if you have a TON of users and over time something has messed with the index... but even then you could just do:

 select employee_id from employees limit 0, 5; (in MySQL)
 select top 5 employee_id from employees (in MSSQL)

And quite frankly... if something's wrong with your indexing or ordering, you have bigger issues...

without your data, and being lazy to not build a fake table and trying it, it's difficult to see exactly what it is returning. If you have direct access to the SQL Server, you can always try to see if you can check the execution plan and try to figure it out that way. Lastly, you can always run a sub query yourself and see what results you get.

[phani1092]

I know that is bizzare way of doing things. I want to retrieve the top 5 rows of the data in oracle without using rownum or rowid. Help me out with this. Thanks in advance.