Nth Largest element from a table sql Query

Question

can any one explain hiow this query works..It's for getting the Nth largest elemet from a table.here it's 4 th largest

SELECT a.ID
  FROM tblitem a
 WHERE (4) = (select count(*)
                from tblItem  b
               where b.id < a.id)

Thanks in advance

Answer 1

(select count(*) from tblItem  b where b.id < a.id)

is a subquery that finds the number of items (count(*)) in the table where its value is less than the current one (a.id).

4 = (select count(*) from tblItem  b where b.id < a.id)

and we pick the one that has exactly 4 items less than the current value. This means a.id should be the 5th smallest.

To select the 4th largest element, change that < into >=.

Answer 2

Perhaps it helps to rewrite the query a bit:

SELECT a.ID,
    (select count(*) from tblItem b where b.id < a.id) as cnt
FROM tblitem a

Example: if the id column starts with 1 and increments with 2, the result will look like:

id   cnt
1    0     No rows with a smaller id, subquery returns 0
3    1     One row with a smaller id, subquery returns 1
5    2     ...
7    3
9    4
11   5

On the fifth row, cnt = 4, and that row is chosen by your where clause.