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

Question

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.

Use like this "Select colname1,colname2 from salaries where emp_no=(select — Shibu
– Shibu, Commented Sep 29, 2018 at 7:41
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
– comphonia, Commented Sep 29, 2018 at 7:59
Possible duplicate of Solution to "subquery returns more than 1 row" error — Raging Bull
– Raging Bull, Commented Sep 29, 2018 at 8:15

Lukasz Szozda · Accepted Answer · 2018-09-29 08:04:26Z

3

If you want second highest value you could use:

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

db<>fiddle demo

answered Sep 29, 2018 at 8:04

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

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 offsets.

trollboy Over a year ago

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.

Strawberry · Accepted Answer · 2018-09-29 08:50:36Z

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

edited Sep 29, 2018 at 8:50

Strawberry

34k14 gold badges43 silver badges58 bronze badges

answered Sep 29, 2018 at 7:38

trollboy

1337 bronze badges

3 Comments

Strawberry Over a year ago

This isn't ugly at all

trollboy Over a year ago

@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.

Strawberry Over a year ago

Potentially, they do different things

comphonia · Accepted Answer · 2018-09-29 08:43:09Z

1

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

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

edited Sep 29, 2018 at 8:43

answered Sep 29, 2018 at 8:23

comphonia

5213 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related