英文:
How to order a specific value to be last and the rest of the records order by id DESC?
问题
我想按id升序对所有记录进行排序，但具有document_status RESULTANT 的记录必须放在最后，看起来像这样：
| id | document_number | document_status |
|---|---|---|
| 1 | 12335 | INFORMED |
| 2 | 4562 | APPROVED |
| 4 | 784456 | APPROVED |
| 5 | 114667 | APPROVED |
| 3 | 467234 | RESULTANT |
这是我尝试过的，但它完全忽略了我的第二个ORDER条件：
SELECT
l.id,
l.document_number,
l.document_status
FROM load_record l
ORDER BY l.id ASC, (l.document_status = 'RESULTANT')
如果我删除l.id ASC，它会起作用，但然后它会将RESULTANT 放在最后，但不会按id ASC对其余记录进行排序。
翻译结果：
SELECT
l.id,
l.document_number,
l.document_status
FROM load_record l
ORDER BY CASE WHEN l.document_status = 'RESULTANT' THEN 1 ELSE 0 END, l.id ASC;
这个查询将根据你的要求对记录进行排序，首先按照document_status 是否为 'RESULTANT' 进行升序排序，然后再按照 id 进行升序排序。这样，RESULTANT 的记录将在最后，而其余记录将按照 id 升序排列。
英文:
| id | document_number | document_status |
|---|---|---|
| 1 | 12335 | INFORMED |
| 2 | 4562 | APPROVED |
| 3 | 467234 | RESULTANT |
| 4 | 784456 | APPROVED |
| 5 | 114667 | APPROVED |
I want to order all the records by id ASC but the one record with document_status RESULTANT has to be last so that it looks like this
| id | document_number | document_status |
|---|---|---|
| 1 | 12335 | INFORMED |
| 2 | 4562 | APPROVED |
| 4 | 784456 | APPROVED |
| 5 | 114667 | APPROVED |
| 3 | 467234 | RESULTANT |
This is what I tried but it completely ignores my second ORDER condition
SELECT
l.id,
l.document_number,
l.document_status
FROM load_record l
ORDER BY l.id ASC, (l.document_status = 'RESULTANT')
It works if I remove the l.id ASC but then it will place the RESULTANT last but it won't order the rest of the records by id ASC
答案1
得分: 1
在ORDER BY子句内，后续的排序字段（在您的情况下，相对于第一个字段的第二个字段）只有在前一个条件相等时才会被考虑，但在您的情况下，“l.id ASC”从未相等，因此“l.document_status = 'RESULTANT'”从未被检查。
如果您颠倒ORDER BY子句中的字段，它应该可以正常工作，因为l.document_status = 'RESULTANT'实际上生成了相等的值，由l.id解开。
ORDER BY l.document_status = 'RESULTANT', l.id
在这里查看演示链接。
注意：您可以省略ASC，因为它是默认的排序方式。
英文:
Inside the ORDER BY clause, the subsequent ordering fields (in your case, the second with respect to the first) are consulted only when the previous condition is tied, but in your case "l.id ASC" is never tied, so "l.document_status = 'RESULTANT'" is never checked.
If you invert the fields inside your ORDER BY clause, it should work properly, because l.document_status = 'RESULTANT' actually generates tied values, untied by l.id.
ORDER BY l.document_status = 'RESULTANT', l.id
Check the demo here.
Note: You can omit ASC, because it's the default ordering value.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论