How to sort data in mysql UNION subquery?

Question

i need to select all vip from table and order them by rand, and then to add other data sorted by date. In first subquery everything is ok, but in second order by spa_date DESC does not work. I know that ORDER BY clauses inside UNION subqueries are ignored without a LIMIT(but order by rand() works), but i need limit from all query (1+2), not from subquery

SELECT * FROM
        (
           select *,DATE_FORMAT(spa_date,"%e %M %Y") as spa_date_out 
            from spa join city using (city_id) 

            where spa_vip=1
            order by RAND() 
         )   as A
        UNION
        (
           select *,DATE_FORMAT(spa_date,"%e %M %Y") as spa_date_out 
            from spa join city using (city_id) 

            where spa_vip=0
            order by spa_date DESC )

        limit 10,10

the question:
i need to select all spa_id where spa_vip=1 and order it by RAND(), then i need to select all spa_id where spa_vip=0 and order it by spa_date, union this 2 subqueries and select data with limit 0,10 and so on for page view

What is the question?. (e.g. Why am I observing this behavior? What is MySQL doing with my statement? How do I get rows returned in a particular order? etc.) Or, were you just giving a status report? When you say the statement doesn't "work", what the plastic does that actually mean? — spencer7593
– spencer7593, Commented May 12, 2015 at 14:11
I've appended a possible SQL solution to the specification in my answer. — spencer7593
– spencer7593, Commented May 12, 2015 at 14:28

spencer7593 · Accepted Answer · 2015-05-12 14:25:37Z

4

I'm not sure any of this answers your question, because I don't see any question being asked.

If you are asking the question "Why am I observing this behavior?", see my answer below.

If you are asking, "What changes do I need to make to my statement so MySQL returns rows in a particular order?", also see my answer below.

If you are asking, "What is the exact statement do I need to run?", we're really just guessing, because it's not clear what order you want the rows returned in, there's no clear specification or description, there's no example data or example output, or rationale for which rows should be returned first.

MySQL is free to return the rows in any order it chooses, because there is no ORDER BY clause on the outermost query. (MySQL could actually ignore the ORDER BY clauses in the inline view queries, and be within spec of the ANSI SQL standard.)

To guarantee that the rows returned by the query will be returned in a particular order, add an ORDER BY clause on the outer query, before the LIMIT clause.

Note that the UNION set operator requires MySQL to check for "duplicate" rows within the combined set, and remove any duplicate rows.

If you don't have a requirement to perform that operation, you can use a UNION ALL set operator in place of the UNION.

In MySQL, it's also possible to omit the SELECT * FROM the beginning of the statement.

I could provide "try this" example SQL, but without knowing what order you actually want the rows returned in, we're just guessing.)

Here's my guess at a query that returns rows in the "order" you are expecting.

I'll show this using the UNION ALL set operator:

    ( 
      SELECT *,DATE_FORMAT(spa_date,"%e %M %Y") as spa_date_out 
        FROM spa
        JOIN city USING (city_id) 
       WHERE spa_vip=1
    )
    UNION ALL
    ( 
      SELECT *,DATE_FORMAT(spa_date,"%e %M %Y") as spa_date_out 
        FROM spa
        JOIN city USING (city_id) 
       WHERE spa_vip=0
    )
    ORDER
       BY spa_vip DESC
        , IF(spa_vip=1,RAND(),0)
        , IF(spa_vip=0,spa_date,NULL) DESC
    LIMIT 10,10

Let's unpack that a little bit. There's two queries that return rows, the UNION ALL set operator combines those two sets into a single set. (If your purpose for using the UNION set operator to remove duplicates, you can get that behavior back by replacing UNION ALL in this query with UNION.)

The first expression in the ORDER BY clause spa_vip gets us the rows from the first set first... we're guaranteed that all rows returned from that first query have a value of 1 for spa_vip, and all rows from the second query have a value of 0.

The second expression returns RAND() for rows from the first query, and a constant for rows from the second query.

The third expression returns a constant for all rows from the first query, and a value from the row for rows from the second query.

If that's the result you are expecting returned, that same result can also be returned by a statement like this:

   SELECT *,DATE_FORMAT(spa_date,"%e %M %Y") as spa_date_out 
     FROM spa
     JOIN city USING (city_id) 
    WHERE spa_vip IN (0,1)
    ORDER
       BY spa_vip DESC
        , IF(spa_vip=1,RAND(),0)
        , IF(spa_vip=0,spa_date,NULL) DESC
    LIMIT 10,10

edited May 12, 2015 at 14:25

answered May 12, 2015 at 14:02

spencer7593

109k15 gold badges122 silver badges148 bronze badges

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

4 Comments

SERG Over a year ago

i need to select all spa_id where spa_vip=1 and order it by RAND(), then i need to select all spa_id where spa_vip=0 and order it by spa_date, union this 2 subqueries and select data with limit 0,10 and so on for page view

spencer7593 Over a year ago

@SERG: I'm happy you found the answer helpful. In ANSI SQL, the only "order by" that matters is the one on the outermost query. (MySQL extends the standard and also honors "order by" in inner queries, some other RDBMS either reject or ignore "order by" clause in a view query.)

spencer7593 Over a year ago

@SERG: One other thing I wanted to point out, if the number of rows in your "first" page (e.g. LIMIT 0,10 = 10 rows) is less than the number of rows returned with spa_vip=1, it's likely that some of those rows will be redisplayed on the second page, and some rows that weren't on the first page will be skipped, since those rows are going to be ordered randomly on each query. (I hope that makes sense to you.)

SERG Over a year ago

i need to display all spa_vip on the first page RAND() (it ranges from 2 to 50), and if number of spa_vip=1 less than 10 i have to summ it with spa_vip=0, and other pages are with spa_vip=0 order by spa_date DESC

Collectives™ on Stack Overflow

How to sort data in mysql UNION subquery?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related