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.

Edited by phani1092: Moved to Databases

3 Years
Discussion Span
Last Post by phani1092
  e.employee_id --get the ids
  employees e --from employees table aliased as e
 ( --begin sub query...
     count(*) --get the total number of rows
     employees f --from employees table aliased as f
     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.


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.

Edited by phani1092

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.