Mixing
Is there a functionality in mysql to order by to specific row?
I have a table e.g students:
id | name
-- |-----
1 | A
2 | B
3 | C
4 | D
5 | E
6 | F
7 | G
8 | H`
How to wite a sql in mysql so that first 4 students are order by id ascending and rest order by id descending output should be something like this
id | name
---|------
1 | A
2 | B
3 | C
4 | D
8 | H
7 | G
6 | F
5 | E
mysql sql sorting sql-order-by
edited Jan 2 at 11:51
Salman A
184k67342441
asked Jan 2 at 5:27
shaanshaan
1507
add a comment |
I have a table e.g students:
id | name
-- |-----
1 | A
2 | B
3 | C
4 | D
5 | E
6 | F
7 | G
8 | H`
How to wite a sql in mysql so that first 4 students are order by id ascending and rest order by id descending output should be something like this
id | name
---|------
1 | A
2 | B
3 | C
4 | D
8 | H
7 | G
6 | F
5 | E
mysql sql sorting sql-order-by
edited Jan 2 at 11:51
Salman A
184k67342441
asked Jan 2 at 5:27
shaanshaan
1507
Yes you can usecasestatement in order by clause
– Kaushik
Jan 2 at 5:33
add a comment |
I have a table e.g students:
id | name
-- |-----
1 | A
2 | B
3 | C
4 | D
5 | E
6 | F
7 | G
8 | H`
How to wite a sql in mysql so that first 4 students are order by id ascending and rest order by id descending output should be something like this
id | name
---|------
1 | A
2 | B
3 | C
4 | D
8 | H
7 | G
6 | F
5 | E
mysql sql sorting sql-order-by
edited Jan 2 at 11:51
Salman A
184k67342441
asked Jan 2 at 5:27
shaanshaan
1507
I have a table e.g students:
id | name
-- |-----
1 | A
2 | B
3 | C
4 | D
5 | E
6 | F
7 | G
8 | H`
How to wite a sql in mysql so that first 4 students are order by id ascending and rest order by id descending output should be something like this
id | name
---|------
1 | A
2 | B
3 | C
4 | D
8 | H
7 | G
6 | F
5 | E
mysql sql sorting sql-order-by
mysql sql sorting sql-order-by
edited Jan 2 at 11:51
Salman A
184k67342441
asked Jan 2 at 5:27
shaanshaan
1507
edited Jan 2 at 11:51
Salman A
184k67342441
asked Jan 2 at 5:27
shaanshaan
1507
edited Jan 2 at 11:51
Salman A
184k67342441
edited Jan 2 at 11:51
Salman A
184k67342441
edited Jan 2 at 11:51
Salman A
184k67342441
184k67342441
asked Jan 2 at 5:27
shaanshaan
1507
asked Jan 2 at 5:27
shaanshaan
1507
asked Jan 2 at 5:27
shaanshaan
1507
1507
Yes you can usecasestatement in order by clause
– Kaushik
Jan 2 at 5:33
add a comment |
Yes you can usecasestatement in order by clause
– Kaushik
Jan 2 at 5:33
Yes you can use
case statement in order by clause– Kaushik
Jan 2 at 5:33
Yes you can use
case statement in order by clause– Kaushik
Jan 2 at 5:33
add a comment |
3 Answers
3
active
oldest
votes
You are using are version of MySQL earlier than 8+, which means that you can't use analytic functions here. Here is one way of doing this, using a correlated subquery with a CASE expression:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN 0
ELSE 1 END,
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN id
ELSE -id END;
Demo
The reason I am calculating the row number above with a correlated subquery, rather than just using the id, is that perhaps your id values may not always start at 1, or even be contiguous. Row number always can correctly target the first row records, as ordered by the id, while the id value itself may not always suffice.
Note that analytic functions make the code a lot easier to read here. Here is what you can do with MySQL 8+:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN 0 ELSE 1 END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN id ELSE -id END;
answered Jan 2 at 5:41
Tim BiegeleisenTim Biegeleisen
233k1399157
I strongly discourage the first approach. Using a correlated subquery to enumerate the rows is very expensive. If you are using MySQL 8+, thenrow_number()is a very good solution.
– Gordon Linoff
Jan 2 at 12:30
@GordonLinoff And I agree with you. If the OP has such requirements long term, then upgrading/switching to a database which supports the basic analytic functions might be in order.
– Tim Biegeleisen
Jan 2 at 12:55
add a comment |
You can use a correlated sub query to calculate the "rank" of the row. Sorting is trivial:
SELECT *, (SELECT COUNT(*) FROM t AS x WHERE id < t.id) AS rn
FROM t
ORDER BY CASE WHEN rn >= 4 THEN -rn END, rn
SELECT COUNT(*) FROM t AS x WHERE id < t.idassigns consecutive numbers to each row starting from 0
CASE WHEN rn >= 4 THEN -rn ENDassigns NULL to rank number 0 ... 3 so secondary sort is required.
answered Jan 2 at 6:50
Salman ASalman A
184k67342441
add a comment |
Based on your sample data, the simplest method would be:
order by (case when id <= 4 then 1 else 2 end),
(case when id <= 4 then id end),
name desc;
If you don't know where the fourth id is, then:
select s.*
from students s cross join
(select s2.id
from students s2
order by s2.id
limit 3, 1
) as s4
order by (case when s.id <= s4.id then 1 else 2 end),
(case when s.id <= s4.id then s.id end),
s.name desc;
answered Jan 2 at 12:26
Gordon LinoffGordon Linoff
788k35313418
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001626%2fis-there-a-functionality-in-mysql-to-order-by-to-specific-row%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
You are using are version of MySQL earlier than 8+, which means that you can't use analytic functions here. Here is one way of doing this, using a correlated subquery with a CASE expression:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN 0
ELSE 1 END,
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN id
ELSE -id END;
Demo
The reason I am calculating the row number above with a correlated subquery, rather than just using the id, is that perhaps your id values may not always start at 1, or even be contiguous. Row number always can correctly target the first row records, as ordered by the id, while the id value itself may not always suffice.
Note that analytic functions make the code a lot easier to read here. Here is what you can do with MySQL 8+:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN 0 ELSE 1 END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN id ELSE -id END;
answered Jan 2 at 5:41
Tim BiegeleisenTim Biegeleisen
233k1399157
I strongly discourage the first approach. Using a correlated subquery to enumerate the rows is very expensive. If you are using MySQL 8+, thenrow_number()is a very good solution.
– Gordon Linoff
Jan 2 at 12:30
@GordonLinoff And I agree with you. If the OP has such requirements long term, then upgrading/switching to a database which supports the basic analytic functions might be in order.
– Tim Biegeleisen
Jan 2 at 12:55
add a comment |
You are using are version of MySQL earlier than 8+, which means that you can't use analytic functions here. Here is one way of doing this, using a correlated subquery with a CASE expression:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN 0
ELSE 1 END,
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN id
ELSE -id END;
Demo
The reason I am calculating the row number above with a correlated subquery, rather than just using the id, is that perhaps your id values may not always start at 1, or even be contiguous. Row number always can correctly target the first row records, as ordered by the id, while the id value itself may not always suffice.
Note that analytic functions make the code a lot easier to read here. Here is what you can do with MySQL 8+:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN 0 ELSE 1 END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN id ELSE -id END;
answered Jan 2 at 5:41
Tim BiegeleisenTim Biegeleisen
233k1399157
I strongly discourage the first approach. Using a correlated subquery to enumerate the rows is very expensive. If you are using MySQL 8+, thenrow_number()is a very good solution.
– Gordon Linoff
Jan 2 at 12:30
@GordonLinoff And I agree with you. If the OP has such requirements long term, then upgrading/switching to a database which supports the basic analytic functions might be in order.
– Tim Biegeleisen
Jan 2 at 12:55
add a comment |
You are using are version of MySQL earlier than 8+, which means that you can't use analytic functions here. Here is one way of doing this, using a correlated subquery with a CASE expression:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN 0
ELSE 1 END,
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN id
ELSE -id END;
Demo
The reason I am calculating the row number above with a correlated subquery, rather than just using the id, is that perhaps your id values may not always start at 1, or even be contiguous. Row number always can correctly target the first row records, as ordered by the id, while the id value itself may not always suffice.
Note that analytic functions make the code a lot easier to read here. Here is what you can do with MySQL 8+:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN 0 ELSE 1 END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN id ELSE -id END;
answered Jan 2 at 5:41
Tim BiegeleisenTim Biegeleisen
233k1399157
You are using are version of MySQL earlier than 8+, which means that you can't use analytic functions here. Here is one way of doing this, using a correlated subquery with a CASE expression:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN 0
ELSE 1 END,
CASE WHEN (SELECT COUNT(*) FROM yourTable t2 WHERE t2.id <= t1.id) <= 4
THEN id
ELSE -id END;
Demo
The reason I am calculating the row number above with a correlated subquery, rather than just using the id, is that perhaps your id values may not always start at 1, or even be contiguous. Row number always can correctly target the first row records, as ordered by the id, while the id value itself may not always suffice.
Note that analytic functions make the code a lot easier to read here. Here is what you can do with MySQL 8+:
SELECT t1.id, t1.name
FROM yourTable t1
ORDER BY
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN 0 ELSE 1 END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY id) <= 4 THEN id ELSE -id END;
answered Jan 2 at 5:41
Tim BiegeleisenTim Biegeleisen
233k1399157
edited Jan 2 at 5:48
answered Jan 2 at 5:41
Tim BiegeleisenTim Biegeleisen
233k1399157
answered Jan 2 at 5:41
Tim BiegeleisenTim Biegeleisen
233k1399157
answered Jan 2 at 5:41
Tim BiegeleisenTim Biegeleisen
233k1399157
233k1399157
I strongly discourage the first approach. Using a correlated subquery to enumerate the rows is very expensive. If you are using MySQL 8+, thenrow_number()is a very good solution.
– Gordon Linoff
Jan 2 at 12:30
@GordonLinoff And I agree with you. If the OP has such requirements long term, then upgrading/switching to a database which supports the basic analytic functions might be in order.
– Tim Biegeleisen
Jan 2 at 12:55
add a comment |
I strongly discourage the first approach. Using a correlated subquery to enumerate the rows is very expensive. If you are using MySQL 8+, thenrow_number()is a very good solution.
– Gordon Linoff
Jan 2 at 12:30
@GordonLinoff And I agree with you. If the OP has such requirements long term, then upgrading/switching to a database which supports the basic analytic functions might be in order.
– Tim Biegeleisen
Jan 2 at 12:55
I strongly discourage the first approach. Using a correlated subquery to enumerate the rows is very expensive. If you are using MySQL 8+, then
row_number() is a very good solution.– Gordon Linoff
Jan 2 at 12:30
I strongly discourage the first approach. Using a correlated subquery to enumerate the rows is very expensive. If you are using MySQL 8+, then
row_number() is a very good solution.– Gordon Linoff
Jan 2 at 12:30
@GordonLinoff And I agree with you. If the OP has such requirements long term, then upgrading/switching to a database which supports the basic analytic functions might be in order.
– Tim Biegeleisen
Jan 2 at 12:55
@GordonLinoff And I agree with you. If the OP has such requirements long term, then upgrading/switching to a database which supports the basic analytic functions might be in order.
– Tim Biegeleisen
Jan 2 at 12:55
add a comment |
You can use a correlated sub query to calculate the "rank" of the row. Sorting is trivial:
SELECT *, (SELECT COUNT(*) FROM t AS x WHERE id < t.id) AS rn
FROM t
ORDER BY CASE WHEN rn >= 4 THEN -rn END, rn
SELECT COUNT(*) FROM t AS x WHERE id < t.idassigns consecutive numbers to each row starting from 0
CASE WHEN rn >= 4 THEN -rn ENDassigns NULL to rank number 0 ... 3 so secondary sort is required.
answered Jan 2 at 6:50
Salman ASalman A
184k67342441
add a comment |
You can use a correlated sub query to calculate the "rank" of the row. Sorting is trivial:
SELECT *, (SELECT COUNT(*) FROM t AS x WHERE id < t.id) AS rn
FROM t
ORDER BY CASE WHEN rn >= 4 THEN -rn END, rn
SELECT COUNT(*) FROM t AS x WHERE id < t.idassigns consecutive numbers to each row starting from 0
CASE WHEN rn >= 4 THEN -rn ENDassigns NULL to rank number 0 ... 3 so secondary sort is required.
answered Jan 2 at 6:50
Salman ASalman A
184k67342441
add a comment |
You can use a correlated sub query to calculate the "rank" of the row. Sorting is trivial:
SELECT *, (SELECT COUNT(*) FROM t AS x WHERE id < t.id) AS rn
FROM t
ORDER BY CASE WHEN rn >= 4 THEN -rn END, rn
SELECT COUNT(*) FROM t AS x WHERE id < t.idassigns consecutive numbers to each row starting from 0
CASE WHEN rn >= 4 THEN -rn ENDassigns NULL to rank number 0 ... 3 so secondary sort is required.
answered Jan 2 at 6:50
Salman ASalman A
184k67342441
You can use a correlated sub query to calculate the "rank" of the row. Sorting is trivial:
SELECT *, (SELECT COUNT(*) FROM t AS x WHERE id < t.id) AS rn
FROM t
ORDER BY CASE WHEN rn >= 4 THEN -rn END, rn
SELECT COUNT(*) FROM t AS x WHERE id < t.idassigns consecutive numbers to each row starting from 0
CASE WHEN rn >= 4 THEN -rn ENDassigns NULL to rank number 0 ... 3 so secondary sort is required.
answered Jan 2 at 6:50
Salman ASalman A
184k67342441
answered Jan 2 at 6:50
Salman ASalman A
184k67342441
answered Jan 2 at 6:50
Salman ASalman A
184k67342441
answered Jan 2 at 6:50
Salman ASalman A
184k67342441
184k67342441
add a comment |
add a comment |
Based on your sample data, the simplest method would be:
order by (case when id <= 4 then 1 else 2 end),
(case when id <= 4 then id end),
name desc;
If you don't know where the fourth id is, then:
select s.*
from students s cross join
(select s2.id
from students s2
order by s2.id
limit 3, 1
) as s4
order by (case when s.id <= s4.id then 1 else 2 end),
(case when s.id <= s4.id then s.id end),
s.name desc;
answered Jan 2 at 12:26
Gordon LinoffGordon Linoff
788k35313418
add a comment |
Based on your sample data, the simplest method would be:
order by (case when id <= 4 then 1 else 2 end),
(case when id <= 4 then id end),
name desc;
If you don't know where the fourth id is, then:
select s.*
from students s cross join
(select s2.id
from students s2
order by s2.id
limit 3, 1
) as s4
order by (case when s.id <= s4.id then 1 else 2 end),
(case when s.id <= s4.id then s.id end),
s.name desc;
answered Jan 2 at 12:26
Gordon LinoffGordon Linoff
788k35313418
add a comment |
Based on your sample data, the simplest method would be:
order by (case when id <= 4 then 1 else 2 end),
(case when id <= 4 then id end),
name desc;
If you don't know where the fourth id is, then:
select s.*
from students s cross join
(select s2.id
from students s2
order by s2.id
limit 3, 1
) as s4
order by (case when s.id <= s4.id then 1 else 2 end),
(case when s.id <= s4.id then s.id end),
s.name desc;
answered Jan 2 at 12:26
Gordon LinoffGordon Linoff
788k35313418
Based on your sample data, the simplest method would be:
order by (case when id <= 4 then 1 else 2 end),
(case when id <= 4 then id end),
name desc;
If you don't know where the fourth id is, then:
select s.*
from students s cross join
(select s2.id
from students s2
order by s2.id
limit 3, 1
) as s4
order by (case when s.id <= s4.id then 1 else 2 end),
(case when s.id <= s4.id then s.id end),
s.name desc;
answered Jan 2 at 12:26
Gordon LinoffGordon Linoff
788k35313418
answered Jan 2 at 12:26
Gordon LinoffGordon Linoff
788k35313418
answered Jan 2 at 12:26
Gordon LinoffGordon Linoff
788k35313418
answered Jan 2 at 12:26
Gordon LinoffGordon Linoff
788k35313418
788k35313418
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001626%2fis-there-a-functionality-in-mysql-to-order-by-to-specific-row%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Yes you can use
casestatement in order by clause– Kaushik
Jan 2 at 5:33