0

i'm trying to write a sql query to list all offers from database but first to retrive priorities NOT NULL order by rand then priorities NULL order by id.

I`ve made some kind of this:

(SELECT anunt_lista_id 
 FROM anunturi__lista
 WHERE anunt_lista_is_prioritar IS NOT NULL
 ORDER BY RAND())
UNION
(SELECT anunt_lista_id
 FROM anunturi__lista
 WHERE anunt_lista_is_prioritar IS NULL
 ORDER BY anunt_lista_id ASC)
LIMIT 100

but ORDER BY is ignored, no one is applied.

There is another way to this and using another way to do RAND() because as i know this is too slow?

Taylan Aydinli
  • 4,333
  • 15
  • 39
  • 33
oriceon
  • 359
  • 2
  • 6
  • 17

2 Answers2

0

You can make dummy columns for order by!

Try this:

SELECT 
 if(anunt_lista_is_prioritar IS NOT NULL, rand() , 0) as fld_1, 
 if(anunt_lista_is_prioritar IS NULL,anunt_lista_id  , 0) as fld_2,
 anunt_lista_id 
 FROM anunturi__lista
 order by fld_1 desc , fld_2 asc
 Limit 100
sourcecode
  • 1,802
  • 2
  • 15
  • 17
0

ORDER BY in individual parts of UNION is only useful when you add a LIMIT clause to that specific part, otherwise it is ignored.

From the docs:

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

If you want the first part of the results to be sorted first, you have to workaround it in some way:

(SELECT anunt_lista_id 
 FROM anunturi__lista 
 WHERE anunt_lista_is_prioritar IS NOT NULL)
UNION
(SELECT anunt_lista_id 
 FROM anunturi__lista 
 WHERE anunt_lista_is_prioritar IS NULL)
ORDER BY (anunt_lista_is_prioritar IS NULL) ASC,
      CASE WHEN anunt_lista_is_prioritar IS NOT NULL THEN 
         RAND() 
      ELSE anunt_lista_id END
LIMIT 100

The first clause of the order by: (anunt_lista_is_prioritar IS NULL) ASC is going to give false (0) when it isn't NULL and true (1) when it is NULL. Since it is ordering ASC, 0 will appear first than 1.

Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • And how can i use another alternatives of RAND() in this case? As i know RAND() is too slowly and is recommended to be avoided. – oriceon Dec 09 '13 at 09:40