Query Problem

V

vijayan

Disciple
Select a.marks from markTable a where (select count(*) from markTable b where b.marks>=a.marks)=3

this is the query for getting the Third highest value.
Can anybody explain how it's working?
 
Its a correlated subquery. For every record returned by the first query (Select a.marks from markTable a), the subquery (select count(*) from markTable b where b.marks>=a.marks) is executed once.

You take the first value (first query), retrieve all the values greater than this (second query) and then compare it with the value specified in the where clause (3).

If there are three values greater than or equal to the value retrieved by the first query, then u get the 3rd largest value...

Maybe some DB guy can explain this better to you [:)]
 
Back
Top