CODEWITHSUNDEEP
mysqlsql
user4955530
user4955530

Reputation: 27

How to use multiple order by in a sql query with limit keyword?

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.

Upvotes: 0

Views: 2331

Answers (3)

trollboy
trollboy

Reputation: 133

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

Upvotes: 2

comphonia
comphonia

Reputation: 521

select * from salaries 
order by salary 
desc limit 1 offset 4;

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

If you want second highest value you could use:

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

db<>fiddle demo

Upvotes: 3

Related Questions