MYSQL UNION Sorting

Question

Can anyone give me some pointers to get this working??

I want to combine to queries into one listing with different sorting. The first will be any business that are a spotlight sorted randomly, then all other listings sorted alphabetically.

This is what I'm using, but the sorting doesn't work.

(SELECT * FROM business_listings WHERE city='Toronto' AND spotlight='1' ORDER by rand())
UNION
(SELECT * FROM business_listings WHERE city='Toronto' AND spotlight='0' ORDER BY busname)

You need to apply an 'order by' clause to the result of the 'union'. It is explained at dev.mysql.com/doc/refman/5.0/en/union.html — Ryan Vincent
– Ryan Vincent, Commented Mar 7, 2014 at 20:22
With a lot of businesses and only few with the spotlight flag it would be much more efficient to split this query into two and join listings in external language. Using union to join and mix them in mysql just to split them back with sophisticated order by rules looks really unnecessary. — piotrm
– piotrm, Commented Mar 7, 2014 at 20:47

Aziz Shaikh · Accepted Answer · 2014-03-07 20:34:48Z

3

You may not need a UNION, use the CASE clause in ORDER BY like this:

SELECT * 
FROM business_listings 
WHERE city='Toronto'
ORDER BY CASE 
  WHEN spotlight='1' THEN FLOOR(RAND()*10) 
  WHEN spotlight='0' THEN 10 
END, busname

What it will do is, sort records using a random value (from 0 to 9) for records having spotlight=1. Whereas all records having spotlight=0 will come later because ORDER BY will use the value 10 as the sort order.

Working Demo: http://sqlfiddle.com/#!2/1bf6e/1

edited Mar 7, 2014 at 20:34

answered Mar 7, 2014 at 20:25

Aziz Shaikh

16.6k11 gold badges65 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:10:56Z

0

from this SO question

Example Fiddle

Try this:

SELECT *
FROM (SELECT * 
      FROM business_listings
      WHERE city='Toronto'
      AND spotlight='1'
      ORDER by rand()) as t1
UNION ALL
SELECT *
FROM (SELECT *
      FROM business_listings
      WHERE city='Toronto'
      AND spotlight='0'
      ORDER BY busname) as t2

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Mar 7, 2014 at 20:25

kschieck

1,4472 gold badges16 silver badges29 bronze badges

Collectives™ on Stack Overflow

MYSQL UNION Sorting

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related