SQL查询按字母顺序对所有结果进行排序，除了一个? [英] SQL query that sorts all results alphabetically except one?

问题描述

我有一个小问题.我正在用SQL数据库的结果填充选择标记.我想要的是添加的最后一个作为顶部条目，其余的按字母顺序排序.

I have a small problem. I'm populating a select tag with results from a SQL database. What I would like to have is the last one added as the top entry and the rest sorted alphabetically.

因此它将返回:

*----------*-------------*
developerID|developerName
*----------*-------------*
| 40       | ZZZ Dev     |
| 39       | A Dev       |
| 38       | Be New Dev  |
*----------*-------------*

当前，它只是按降序选择所有条目:

Currently it just selects all the entries in descending order:

"SELECT developerName, developerID FROM developer ORDER BY developerID DESC"

哪个还可以，但是没有我想要的有用.

Which is fine, but not as usable as I would like.

我想使用纯MySQL实现什么?

Is what I want possible using pure MySQL?

推荐答案

假设定义添加开发人员的时间的列是developerAdded(某种时间戳):

Assuming the column defining when the developer was added is developerAdded (a timestamp of some sort):

SELECT developerName, developerID, 1 AS ordering
    FROM developer
    WHERE developerAdded =  (SELECT MAX(developerAdded)
                                FROM developer)
UNION
SELECT developerName, developerID, 2 AS ordering
    FROM developer
    WHERE developerAdded != (SELECT MAX(developerAdded)
                                FROM developer)
ORDER BY ordering, developername;

如果还有其他神奇的方法来确定最近添加的开发者(例如，最大的developerID)，请相应地调整子查询.

If there's some other magic way to determine the most recently added developer (e.g. maximum developerID), adjust the sub-queries accordingly.

请注意，即使几个开发人员满足最近添加的"条件，此查询也可以正常工作；它们会按字母顺序显示在已有较长时间的开发人员之前.

Note that this query works OK even if several developers satisfy the 'most recently added' criterion; they appear in alphabetic order before the developers who've been around longer.

由于最近添加的开发人员具有最大的developerID，因此修改后的查询应为:

Since the most recently added developer has the maximum developerID, the revised query should be:

SELECT developerName, developerID, 1 AS ordering
    FROM developer
    WHERE developerID =  (SELECT MAX(developerID) FROM developer)
UNION
SELECT developerName, developerID, 2 AS ordering
    FROM developer
    WHERE developerID != (SELECT MAX(developerID) FROM developer)
ORDER BY ordering, developername;

这篇关于SQL查询按字母顺序对所有结果进行排序，除了一个?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！