CODEWITHSUNDEEP
mysql
different
different

Reputation: 2373

SQL query that sorts all results alphabetically except one?

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.

So it would return:

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

Is what I want possible using pure MySQL?

Upvotes: 2

Views: 6017

Answers (5)

Ron Savage
Ron Savage

Reputation: 11079

I would recommend against trying to alter the sort order of the items in your list, as that changes the "expected behavior" your users will have about the list.

If the most common use of that page usually deals the "last added" name, you could make that the "default" selection in the list. So when the page comes up, the last added entry is already selected in the list - but if they open the list all the entries are still there alphabetically and it will behave as they expect.

That way your query stays simple, but you achieve your "usability" goal as well.

Upvotes: 1

Schmutzka
Schmutzka

Reputation: 11

Another kind of solution WHERE main is the timestamp, which sets the FIRST item, others are sort alphabetically by name of the place.

ORDER BY main=(SELECT MAX(main) FROM places WHERE user_ID = 7) DESC,place ASCSELECT ID,place,main FROM places WHERE user_ID = 7

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 754420

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;

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.

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;

Upvotes: 9

MarkusQ
MarkusQ

Reputation: 21950

SELECT developerName, developerID 
  FROM developer 
  ORDER BY developerID = {last_used},developerID DESC

Or some variant there of. The idea being to do a two part sort, with the first segment pulling out the last one used.

-- MarkusQ

On edit: reversed the sense (added not) 'cause you want it at the top.

Second edit, reverted my first edit in response to your edit of the question.

Upvotes: 0

kquinn
kquinn

Reputation: 10740

I think the best way to do this is just with two queries. Display the most recent item separate from the regular alphabetical list, where it will also appear (so it's shown twice). This is easy to do and arguably more intuitive and usable anyway.

Otherwise, if you're really insistent on doing it your way and with a single SQL query, you can use UNION ALL and INTERSECT or EXCEPT to cook something up. But MySQL doesn't preserve any sort of order in the results of a UNION (see http://dev.mysql.com/doc/refman/5.1/en/union.html), and so such a query is likely to get ugly fast.

Upvotes: 2

Related Questions