Alternate to ORDER BY subquery

Question

From the database at http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_orderby2, for example (Click "Run SQL"), I want to list the customer who has the largest CustomerID that is greater than 80 first among a list of all customers from USA. So I use

SELECT * FROM Customers
WHERE Country = 'USA'
ORDER BY CustomerID = (SELECT MAX(CustomerID) FROM Customers 
    WHERE CustomerID > 80 AND Country = 'USA') DESC, PostalCode;

but this is not the real query I'm using. If the SELECT... FROM... WHERE... portion of the query is more complex, what is a more elegant query?

The actual query I am trying to modify is

SELECT post.postid, post.visible, post.userid, post.parentid, post.vote_count
FROM " . TABLE_PREFIX . "post AS post
WHERE post.threadid = $threadid
AND post.visible IN (1" . (!empty($deljoin) ? ", 2" : "") . 
    ($show['approvepost'] ? ", 0" : "") . ")
ORDER BY post.postid = {$threadinfo['firstpostid']} DESC, post.vote_count > 5 DESC, 
    post.dateline $postorder

where the post.vote_count > 5 DESC portion I am trying to replace with only the largest post.vote_count that is larger than 5. So I use:

SELECT post.postid, post.visible, post.userid, post.parentid, post.vote_count
FROM " . TABLE_PREFIX . "post AS post
WHERE post.threadid = $threadid
AND post.visible IN (1" . (!empty($deljoin) ? ", 2" : "") . 
    ($show['approvepost'] ? ", 0" : "") . ")
ORDER BY post.postid = {$threadinfo['firstpostid']} DESC, post.vote_count = (
    SELECT MAX(post.vote_count)
    FROM " . TABLE_PREFIX . "post AS post
    WHERE post.threadid = $threadid
    AND post.visible IN (1" . (!empty($deljoin) ? ", 2" : "") . 
        ($show['approvepost'] ? ", 0" : "") . ")
    AND post.vote_count > 5
)
DESC, post.dateline $postorder

and all is good. But you can imagine a more complex query, perhaps with INNER JOIN, whose SELECT... FROM... WHERE... etc. must all be duplicated in the subquery.

So my question is, I suspect, can you order query results so the first item (within those results) has the maximum of a field, and the remainder ordered otherwise, without essentially rewriting the entire query in a subquery?

How large is your database? The query isn't overly complex, it's strange that it should take a long time. Could you run an `EXPLAIN`? — Benoit Garret, Apr 07 '15 at 09:45
@Benoit Garret Good point. I've hopefully elucidated my question. — zylstra, Apr 07 '15 at 17:27

score 1 · Answer 1 · answered Apr 08 '15 at 09:27

MySQL does not support CTE's, which would have been the perfect solution to simplify your query. They can be emulated with a view though :

CREATE VIEW c AS (SELECT Customer.* FROM Customer WHERE Country = "USA");
SELECT c.* FROM c ORDER BY CustomerID = (SELECT MAX(CustomerID) FROM c) DESC;
DROP VIEW c;

In your case, this would give :

CREATE VIEW p AS (
    SELECT post.postid, post.visible, post.userid, post.parentid, post.vote_count
    FROM " . TABLE_PREFIX . "post AS post
    WHERE post.threadid = $threadid
    AND post.visible IN (1" . (!empty($deljoin) ? ", 2" : "") . 
        ($show['approvepost'] ? ", 0" : "") . ")
);
SELECT p.* FROM p
ORDER BY p.postid = {$threadinfo['firstpostid']} DESC, p.vote_count = (
    SELECT MAX(p.vote_count)
    FROM p
    WHERE p.vote_count > 5
)
DESC, post.dateline $postorder;
DROP VIEW p;

score -2 · Answer 2 · answered Apr 07 '15 at 09:43

-2

This query will list data in descending order of CustomerID

 SELECT * FROM Customers
    WHERE Country = 'USA'
    ORDER BY CustomerID  DESC, PostalCode;

answered Apr 07 '15 at 09:43

abhi

794
5
15
28

2

This does not answer the question. The question was about listing with a filter, and inserting this in another query. – Eregrith Apr 07 '15 at 10:39

Alternate to ORDER BY subquery

2 Answers2