0

I want to get nth highest salary in a table by first limiting result set to n top salaries first and then to reverse the result set and limiting it to 1.

I have used this query which is resulting into an error:- 

select *
  from salaries
 where emp_no=(select * from salaries order by salary desc limit 2) order by salary asc limit 1;

The error states that subquery return more than one row.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
user4955530
  • 27
  • 5
  • 2
    Tip: NEVER use `SELECT *` – Strawberry Sep 29 '18 at 07:34
  • Use like this "Select colname1,colname2 from salaries where emp_no=(select – Shibu Sep 29 '18 at 07:41
  • 1
    seems like a complicated solution for a simple problem. say you want to get the 5th top salary, query the salary column, order it by asc limit 1 offset 4 – comphonia Sep 29 '18 at 07:59
  • 1
    Possible duplicate of [Solution to "subquery returns more than 1 row" error](https://stackoverflow.com/questions/28171474/solution-to-subquery-returns-more-than-1-row-error) – Raging Bull Sep 29 '18 at 08:15
  • Possible duplicate of [MYSQL - ORDER BY & LIMIT](https://stackoverflow.com/questions/4708708/mysql-order-by-limit) – Bud Damyanov Sep 29 '18 at 08:48

3 Answers3

3

If you want second highest value you could use:

SELECT *
FROM salaries
ORDER BY salary DESC
LIMIT 1,1;

db<>fiddle demo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    I am upvoting because this does functionally what the OP wants and is the best solution (and is the first answer with that solution). It should probably explain that using a subquery with two sorts is *not* the best solution in any database that supports `offset`s. – Gordon Linoff Sep 29 '18 at 11:42
  • 1
    Fully agreed and upvoting your comment! My solution accomplishes the task using subqueries only because I wanted to also illustrate a proper way to use subqueries. When in doubt always get your solution doing as few queries as possible. – trollboy Sep 29 '18 at 19:13
2

Here's one way:

SELECT s.* FROM 
(SELECT * FROM `salaries` ORDER BY salary DESC LIMIT 2) s
 ORDER BY s.salary ASC LIMIT 1;

Never use SELECT *, see http://www.parseerror.com/blog/select-*-is-evil

Strawberry
  • 33,750
  • 13
  • 40
  • 57
trollboy
  • 133
  • 7
  • This isn't ugly at all – Strawberry Sep 29 '18 at 08:50
  • @lukasz-szozda 's answer below that I upvoted is a far more elegant solution. I was more trying to make his query work as requested. My assumption was that there was some other reasons for using the sub-select (learning sub-selects, etc) which is why I kept it and merely implemented it correctly. I consider unnecessary queries as ugly and focus on efficiency. Sorry if my verbiage offended. – trollboy Sep 29 '18 at 09:00
  • Potentially, they do different things – Strawberry Sep 29 '18 at 10:16
1
select * from salaries 
order by salary 
desc limit 1 offset 4;

https://www.db-fiddle.com/f/2itHSFs2enyNpJ3MK6Nxcz/0

comphonia
  • 521
  • 3
  • 10