Nth Largest element from a table sql Query
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
开发者_如何学Go where b.id < a.id)
Thanks in advance
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.
(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 >=
.
精彩评论