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

问题:

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

回答1:

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;

enter image description here

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;

回答2:

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.id assigns consecutive numbers to each row starting from 0
  • CASE WHEN rn >= 4 THEN -rn END assigns NULL to rank number 0 ... 3 so secondary sort is required.

回答3:

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;
  • 发表于 2019-01-07 17:35
  • 阅读 ( 208 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除