<p>I want to find 2<sup>nd</sup>, 3<sup>rd</sup>, ... n<sup>th</sup> maximum value of a column.</p>

[{'answer_id': 80720, 'author': 'TK.', 'author_id': 1816, 'author_profile': 'https://Stackoverflow.com/users/1816', 'pm_score': 5, 'selected': True, 'text': '<p>You could sort the column into descending format and then just obtain the value from the nth row.</p>\n\n<p>EDIT::</p>\n\n<p>Updated as per comment request. <strong><em>WARNING</em></strong> completely untested!</p>\n\n<pre><code>SELECT DOB FROM (SELECT DOB FROM USERS ORDER BY DOB DESC) WHERE ROWID = 6\n</code></pre>\n\n<p>Something like the above should work for Oracle ... you might have to get the syntax right first!</p>\n'}, {'answer_id': 80734, 'author': 'Pieter', 'author_id': 5822, 'author_profile': 'https://Stackoverflow.com/users/5822', 'pm_score': 4, 'selected': False, 'text': "<p>You didn't specify which database, on MySQL you can do</p>\n\n<pre><code>SELECT column FROM table ORDER BY column DESC LIMIT 7,10;\n</code></pre>\n\n<p>Would skip the first 7, and then get you the next ten highest.</p>\n"}, {'answer_id': 80771, 'author': 'Blorgbeard', 'author_id': 369, 'author_profile': 'https://Stackoverflow.com/users/369', 'pm_score': 0, 'selected': False, 'text': "<p>In SQL Server, just do:</p>\n\n<pre><code>select distinct top n+1 column from table order by column desc\n</code></pre>\n\n<p>And then throw away the first value, if you don't need it.</p>\n"}, {'answer_id': 81088, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': "<p>Again you may need to fix for your database, but if you want the top 2nd value in a dataset that potentially has the value duplicated, you'll want to do a group as well:</p>\n\n<pre><code>SELECT column \nFROM table \nWHERE column IS NOT NULL \nGROUP BY column \nORDER BY column DESC \nLIMIT 5 OFFSET 2;\n</code></pre>\n\n<p>Would skip the first two, and then will get you the next five highest.  </p>\n"}, {'answer_id': 82609, 'author': 'Matt Rogish', 'author_id': 2590, 'author_profile': 'https://Stackoverflow.com/users/2590', 'pm_score': 3, 'selected': False, 'text': '<p>Pure SQL (note: I would recommend using SQL features specific to your DBMS since it will be likely more efficient). This will get you the n+1th largest value (to get smallest, flip the &lt;). If you have duplicates, make it COUNT( DISTINCT VALUE )..</p>\n\n<pre><code>select id from table order by id desc limit 4 ;\n+------+\n| id   |\n+------+\n| 2211 | \n| 2210 | \n| 2209 | \n| 2208 | \n+------+\n\n\nSELECT yourvalue\n  FROM yourtable t1\n WHERE EXISTS( SELECT COUNT(*)\n                 FROM yourtable t2\n                WHERE t1.id       &lt;&gt; t2.id\n                  AND t1.yourvalue &lt; t2.yourvalue\n               HAVING COUNT(*) = 3 )\n\n\n+------+\n| id   |\n+------+\n| 2208 | \n+------+\n</code></pre>\n'}, {'answer_id': 82829, 'author': 'Dave Costa', 'author_id': 6568, 'author_profile': 'https://Stackoverflow.com/users/6568', 'pm_score': 1, 'selected': False, 'text': "<p>Here's a method for Oracle.  This example gets the 9th highest value.  Simply replace the 9 with a bind variable containing the position you are looking for.</p>\n\n<pre><code>   select created from (\n     select created from (\n       select created from user_objects\n         order by created desc\n       )\n       where rownum &lt;= 9\n       order by created asc\n     )\n     where rownum = 1\n</code></pre>\n\n<p>If you wanted the nth unique value, you would add DISTINCT on the innermost query block.</p>\n"}, {'answer_id': 83170, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>for SQL 2005:</p>\n\n<pre><code>SELECT col1 from \n     (select col1, dense_rank(col1) over (order by col1 desc) ranking \n     from t1) subq where ranking between 2 and @n\n</code></pre>\n'}, {'answer_id': 86447, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>Another one for Oracle using analytic functions:</p>\n\n<pre><code>select distinct col1 --distinct is required to remove matching value of column\nfrom \n( select col1, dense_rank() over (order by col1 desc) rnk\n  from tbl\n)\nwhere rnk = :b1\n</code></pre>\n'}, {'answer_id': 734650, 'author': 'Phil H', 'author_id': 36537, 'author_profile': 'https://Stackoverflow.com/users/36537', 'pm_score': 1, 'selected': False, 'text': '<p>Just dug out this question when looking for the answer myself, and this seems to work for SQL Server 2005 (derived from <a href="https://stackoverflow.com/questions/80706/query-to-find-nth-max-value-of-a-column/80771#80771">Blorgbeard\'s solution</a>):</p>\n\n<pre><code>SELECT MIN(q.col1) FROM (\n    SELECT\n        DISTINCT TOP n col1\n        FROM myTable\n        ORDER BY col1 DESC\n) q;\n</code></pre>\n\n<p>Effectively, that is a <code>SELECT MIN(q.someCol) FROM someTable q</code>, with the top n of the table retrieved by the <code>SELECT DISTINCT...</code> query.</p>\n'}, {'answer_id': 750539, 'author': 'dexter', 'author_id': 1385252, 'author_profile': 'https://Stackoverflow.com/users/1385252', 'pm_score': 5, 'selected': False, 'text': '<p>Consider the following Employee table with a single column for salary.</p>\n\n<pre>\n+------+\n| Sal  |\n+------+\n| 3500 | \n| 2500 | \n| 2500 | \n| 5500 |\n| 7500 |\n+------+\n</pre>\n\n<p>The following query will return the Nth Maximum element.</p>\n\n<pre><code>select SAL from EMPLOYEE E1 where \n (N - 1) = (select count(distinct(SAL)) \n            from EMPLOYEE E2 \n            where E2.SAL &gt; E1.SAL )\n</code></pre>\n\n<p>For eg. when the second maximum value is required,</p>\n\n<pre><code>  select SAL from EMPLOYEE E1 where \n     (2 - 1) = (select count(distinct(SAL)) \n                from EMPLOYEE E2 \n                where E2.SAL &gt; E1.SAL )\n</code></pre>\n\n<pre>\n+------+\n| Sal  |\n+------+\n| 5500 |\n+------+\n</pre>\n'}, {'answer_id': 815171, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<pre><code>select sal,ename from emp e where\n 2=(select count(distinct sal) from emp  where e.sal&lt;=emp.sal) or\n 3=(select count(distinct sal) from emp  where e.sal&lt;=emp.sal) or\n 4=(select count(distinct sal) from emp  where e.sal&lt;=emp.sal) order by sal desc;\n</code></pre>\n'}, {'answer_id': 1730110, 'author': 'Piyush', 'author_id': 210562, 'author_profile': 'https://Stackoverflow.com/users/210562', 'pm_score': 1, 'selected': False, 'text': '<pre><code>Select max(sal) \nfrom table t1 \nwhere N (select max(sal) \n        from table t2 \n        where t2.sal &gt; t1.sal)\n</code></pre>\n\n<p>To find the Nth max sal.</p>\n'}, {'answer_id': 4182477, 'author': 'shankar', 'author_id': 507987, 'author_profile': 'https://Stackoverflow.com/users/507987', 'pm_score': 1, 'selected': False, 'text': '<pre><code>SELECT * FROM tablename \nWHERE columnname&lt;(select max(columnname) from tablename) \norder by columnname desc limit 1\n</code></pre>\n'}, {'answer_id': 4538635, 'author': 'Ritesh', 'author_id': 554948, 'author_profile': 'https://Stackoverflow.com/users/554948', 'pm_score': 0, 'selected': False, 'text': '<p>MySQL:</p>\n\n<pre><code>select distinct(salary) from employee order by salary desc limit (n-1), 1;\n</code></pre>\n'}, {'answer_id': 12241522, 'author': 'Raman kumar', 'author_id': 1642680, 'author_profile': 'https://Stackoverflow.com/users/1642680', 'pm_score': -1, 'selected': False, 'text': '<p>Table employee</p>\n\n<pre><code>salary \n1256\n1256\n2563\n8546\n5645\n</code></pre>\n\n<p>You find the second max value by this query</p>\n\n<pre><code>select salary \nfrom employee \nwhere salary=(select max(salary) \n                from employee \n                where salary &lt;(select max(salary) from employee));\n</code></pre>\n\n<p>You find the third max value by this query</p>\n\n<pre><code>select salary \nfrom employee \nwhere salary=(select max(salary) \n                from employee \n                where salary &lt;(select max(salary) \n                                from employee \n                                where salary &lt;(select max(salary)from employee)));\n</code></pre>\n'}, {'answer_id': 12318458, 'author': 'parveen', 'author_id': 1654762, 'author_profile': 'https://Stackoverflow.com/users/1654762', 'pm_score': 0, 'selected': False, 'text': '<p>Answer :\ntop second:</p>\n\n<pre><code>select * from (select * from deletetable   where rownum &lt;=2 order by rownum desc) where rownum &lt;=1\n</code></pre>\n'}, {'answer_id': 13466974, 'author': 'German Alex', 'author_id': 1837741, 'author_profile': 'https://Stackoverflow.com/users/1837741', 'pm_score': 2, 'selected': False, 'text': '<p>(Table Name=Student, Column Name= mark)</p>\n\n<pre><code>select * from(select row_number() over (order by mark desc) as t,mark from student group by mark) as td where t=4\n</code></pre>\n'}, {'answer_id': 13468939, 'author': 'German Alex', 'author_id': 1837741, 'author_profile': 'https://Stackoverflow.com/users/1837741', 'pm_score': 0, 'selected': False, 'text': '<p>(TableName=Student, ColumnName=Mark) :</p>\n\n<pre><code>select *\nfrom student \nwhere mark=(select mark \n            from(select row_number() over (order by mark desc) as t,\n                 mark \n                 from student group by mark) as td \n            where t=2)\n</code></pre>\n'}, {'answer_id': 14473660, 'author': 'Abhishek B Patel', 'author_id': 2001168, 'author_profile': 'https://Stackoverflow.com/users/2001168', 'pm_score': 2, 'selected': False, 'text': '<p>You can find the nth largest value of column by using the following query:</p>\n\n<pre><code>SELECT * FROM TableName a WHERE\n    n = (SELECT count(DISTINCT(b.ColumnName)) \n    FROM TableName b WHERE a.ColumnName &lt;=b.ColumnName);\n</code></pre>\n'}, {'answer_id': 17875240, 'author': 'ria', 'author_id': 2621681, 'author_profile': 'https://Stackoverflow.com/users/2621681', 'pm_score': 0, 'selected': False, 'text': '<p>I think that the query below will work just perfect on oracle sql...I have tested it myself..</p>\n\n<p>Info related to this query : this query is using two tables named <code>employee</code> and <code>department</code> with columns in employee named: <code>name</code> (employee name), <code>dept_id</code> (common to employee and department), <code>salary</code></p>\n\n<p>And columns in department table: <code>dept_id</code> (common for employee table as well), <code>dept_name</code></p>\n\n<pre><code>SELECT\n  tab.dept_name,MIN(tab.salary) AS Second_Max_Sal FROM (\n    SELECT e.name, e.salary, d.dept_name, dense_rank() over (partition BY  d.dept_name          ORDER BY e.salary)  AS   rank FROM department d JOIN employee e USING (dept_id) )  tab\n WHERE\n   rank  BETWEEN 1 AND 2\n GROUP BY\n   tab.dept_name\n</code></pre>\n\n<p>thanks</p>\n'}, {'answer_id': 20630590, 'author': 'user3110552', 'author_id': 3110552, 'author_profile': 'https://Stackoverflow.com/users/3110552', 'pm_score': 0, 'selected': False, 'text': '<pre><code>Select min(fee) \nfrom fl_FLFee \nwhere fee in (Select top 4 Fee from fl_FLFee order by 1 desc)\n</code></pre>\n\n<p>Change Number four with N.</p>\n'}, {'answer_id': 24801342, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>You can simplify like this </p>\n\n<pre><code>SELECT MIN(Sal) FROM TableName\nWHERE Sal IN\n(SELECT TOP 4 Sal FROM TableName ORDER BY Sal DESC)\n</code></pre>\n\n<p>If the Sal contains duplicate values then use this</p>\n\n<pre><code>SELECT MIN(Sal) FROM TableName\nWHERE Sal IN\n(SELECT distinct TOP 4 Sal FROM TableName ORDER BY Sal DESC)\n</code></pre>\n\n<p>the 4 will be nth value it may any highest value such as 5 or 6 etc.</p>\n'}, {'answer_id': 27187656, 'author': 'Prashant Maheshwari Andro', 'author_id': 2646705, 'author_profile': 'https://Stackoverflow.com/users/2646705', 'pm_score': 1, 'selected': False, 'text': '<p>This is query for getting  nth Highest from colomn put n=0 for second highest and n= 1 for 3rd highest and so on...</p>\n\n<pre><code> SELECT * FROM TableName\n WHERE ColomnName&lt;(select max(ColomnName) from TableName)-n order by ColomnName desc limit 1;\n</code></pre>\n'}, {'answer_id': 46322804, 'author': 'Rahul Raina', 'author_id': 2828087, 'author_profile': 'https://Stackoverflow.com/users/2828087', 'pm_score': 1, 'selected': False, 'text': '<p><strong>Simple SQL Query to get the employee detail who has Nth MAX <code>Salary</code> in the table <code>Employee</code>.</strong></p>\n\n<pre><code>sql&gt; select * from Employee order by salary desc LIMIT 1 OFFSET &lt;N - 1&gt;;\n</code></pre>\n\n<p><em>Consider table structure as:</em></p>\n\n<blockquote>\n  <p>Employee ( \n        id [int primary key auto_increment], \n        name [varchar(30)], \n        salary [int] );</p>\n</blockquote>\n\n<p><strong>Example:</strong></p>\n\n<p>If you need 3rd <code>MAX</code> salary in the above table then, query will be:</p>\n\n<pre><code>sql&gt; select * from Employee order by salary desc LIMIT 1 OFFSET 2;\n</code></pre>\n\n<p><strong>Similarly:</strong></p>\n\n<p>If you need 8th <code>MAX</code> salary in the above table then, query will be:</p>\n\n<pre><code>sql&gt; select * from Employee order by salary desc LIMIT 1 OFFSET 7;\n</code></pre>\n\n<blockquote>\n  <p><strong>NOTE:</strong>\n  When you have to get the <em>Nth</em> <code>MAX</code> value you should give the <code>OFFSET</code> as <em>(N - 1)</em>.</p>\n</blockquote>\n\n<p><em>Like this you can do same kind of operation in case of salary in ascending order.</em></p>\n'}, {'answer_id': 46408544, 'author': 'rashedcs', 'author_id': 6714430, 'author_profile': 'https://Stackoverflow.com/users/6714430', 'pm_score': 2, 'selected': False, 'text': '<pre><code>select column_name from table_name \norder by column_name desc limit n-1,1;\n</code></pre>\n\n<p>where n = 1, 2, 3,....nth max value.</p>\n'}, {'answer_id': 48053324, 'author': 'Trung Lê Hoàng', 'author_id': 4902809, 'author_profile': 'https://Stackoverflow.com/users/4902809', 'pm_score': 0, 'selected': False, 'text': '<p>In PostgreSQL, to find N-th largest salary from Employee table.</p>\n\n<pre><code>SELECT * FROM Employee WHERE salary in \n(SELECT salary FROM Employee ORDER BY salary DESC LIMIT N) \nORDER BY salary ASC LIMIT 1;\n</code></pre>\n'}, {'answer_id': 51723878, 'author': 'mjp', 'author_id': 9253770, 'author_profile': 'https://Stackoverflow.com/users/9253770', 'pm_score': 1, 'selected': False, 'text': '<p><strong>mysql query</strong>:\n<em>suppose i want to find out nth max salary form employee table</em></p>\n\n<pre><code>select salary \nform employee\norder by salary desc\nlimit n-1,1 ;\n</code></pre>\n'}, {'answer_id': 52591150, 'author': 'ARSHAD M', 'author_id': 9196559, 'author_profile': 'https://Stackoverflow.com/users/9196559', 'pm_score': 0, 'selected': False, 'text': '<p>Solution to find Nth Maximum value of a particular column in SQL Server:</p>\n\n<p>Employee table:</p>\n\n<p><img src="https://i.stack.imgur.com/nMqUu.jpg" alt="Employee Table"></p>\n\n<p>Sales table:</p>\n\n<p><img src="https://i.stack.imgur.com/SqdMi.jpg" alt="Sales Table"></p>\n\n<p>Employee table data:</p>\n\n<pre><code>==========\nId  name\n=========\n6   ARSHAD M\n7   Manu\n8   Shaji\n</code></pre>\n\n<p>Sales table data:</p>\n\n<pre><code>=================\nid  emp_id   amount\n=================\n1   6        500\n2   7        100\n3   8        100\n4   6        150\n5   7        130\n6   7        130\n7   7        330\n</code></pre>\n\n<p>Query to Find out details of an employee who have highest sale/ <strong>N</strong>th highest salesperson</p>\n\n<pre><code>select * from (select E.Id,E.name,SUM(S.amount) AS \'total_amount\' from employee E INNER JOIN Sale S on E.Id=S.emp_id group by S.emp_id,E.Id,E.name ) AS T1 WHERE(0)=( select COUNT(DISTINCT(total_amount)) from(select E.Id,E.name,SUM(S.amount) AS \'total_amount\' from employee E INNER JOIN Sale S on E.Id=S.emp_id group by S.emp_id,E.Id,E.name )AS T2 WHERE(T1.total_amount&lt;T2.total_amount) );\n</code></pre>\n\n<p>In the <strong>WHERE(0)</strong> replace 0 by n-1</p>\n\n<p>Result:</p>\n\n<pre><code>========================\nid  name    total_amount\n========================\n7   Manu    690\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/80706', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/15181/']