I wish to find second,3rd..nth maximum worth of a column
You can sort the column into climbing down format after which just have the value in the nth row.
Up-to-date according to comment request. WARNING completely untested!
SELECT DOB FROM (SELECT DOB FROM USERS ORDER BY DOB DESC) WHERE ROWID = 6
Something similar to the above mentioned should work with Oracle ... you may have to obtain the syntax right first!
You did not specify which database, on MySQL that you can do
SELECT column FROM table ORDER BY column DESC LIMIT 7,10;
Would skip the very first 7, after which enable you to get the following ten greatest.
Pure SQL (note: I would suggest using SQL features specific for your DBMS since it will likely be likely more effective). This can enable you to get the n+1th biggest value (to obtain littlest, switch the <). For those who have replicates, allow it to be COUNT( DISTINCT VALUE )..
select id from table order by id desc limit 4 ; +------+ | id | +------+ | 2211 | | 2210 | | 2209 | | 2208 | +------+ SELECT yourvalue FROM yourtable t1 WHERE EXISTS( SELECT COUNT(*) FROM yourtable t2 WHERE t1.id <> t2.id AND t1.yourvalue < t2.yourvalue HAVING COUNT(*) = 3 ) +------+ | id | +------+ | 2208 | +------+
Here is a way of Oracle. This situation will get the ninth greatest value. Simply replace the 9 having a bind variable that contains the positioning you're searching for.
select created from ( select created from ( select created from user_objects order by created desc ) where rownum <= 9 order by created asc ) where rownum = 1
Should you wanted the nth unique value, you'd add DISTINCT around the innermost query block.
Think about the following Worker table having a single column for salary.
+------+ Sal +------+ 3500 2500 2500 5500 7500 +------+
The next query will return the Nth Maximum element.
select SAL from EMPLOYEE E1 where (N - 1) = (select count(distinct(SAL)) from EMPLOYEE E2 where E2.SAL > E1.SAL )
For eg. once the second maximum value is needed,
select SAL from EMPLOYEE E1 where (2 - 1) = (select count(distinct(SAL)) from EMPLOYEE E2 where E2.SAL > E1.SAL )
+------+ Sal +------+ 5500 +------+
What database? I do not think there's an excellent "generic" fix for your problem.
In SQL Server, simply do:
select distinct top n+1 column from table order by column desc
After which discard the very first value, if you do not require it.
Again you may want to treatment for your database, but when you would like the very best second value inside a dataset that potentially has got the value copied, you will want to perform a group too:
Choose column FROM table WHERE column Isn't NULL GROUP BY column ORDER BY column DESC LIMIT 5 OFFSET 2
Would skip the very first two, after which enable you to get the following seven greatest.
for SQL 2005:
SELECT col1 from (select col1, dense_rank(col1) over (order by col1 desc) ranking from t1) subq where ranking between 2 and @n
A different one for Oracle using analytic functions:
select col1 from ( select col1, dense_rank() over (order by col1 desc) rnk from tbl ) where rnk = :b1
Just dug out this when searching for the solution myself, which appears to dedicate yourself SQL Server 2005 (based on Blorgbeard's solution):
SELECT MIN(q.col1) FROM ( SELECT DISTINCT TOP n col1 FROM myTable ORDER BY col1 DESC ) q;
Effectively, that's a
SELECT MIN(q.someCol) FROM someTable q, using the top n on the table retrieved through the
SELECT DISTINCT... query.