CODEWITHSUNDEEP
mysql
Gowri
Gowri

Reputation: 16835

mysql Rand() not working

In my case i need to display particular item at the first, other than that i need to select randomly . so i used the following query 

"SELECT * FROM table_test1 WHERE id = 3  UNION( SELECT * FROM table_test1 WHERE id <> 3   ORDER BY RAND() ) "

But it doesn't selects randomly

my table

id  name
1    A
2    B
3    C
4    D
5    E

this selects always C-A-B-D-E

can any one explain theory of union ?

Whats wrong here

Upvotes: 1

Views: 431

Answers (2)

Martin Smith
Martin Smith

Reputation: 453287

Use

SELECT *
FROM   table_test1
ORDER  BY CASE
            WHEN id = 3 THEN -1
            ELSE RAND()
          END

As Quassnoi points out in the comments the ORDER BY in the second statement is completely ignored.

Semantically if you want the whole results to be sorted by a particular order you need an order by that applies to the whole query anyway or your solution might end up relying on assumptions about implementation that break in future versions.

Upvotes: 1

Isotopp
Isotopp

Reputation: 3383

UNION deletes duplicates. To do that, it sorts the result set, hence the order you get. You can get around that using UNION ALL, which does not delete duplicates, and hence does not need to sort.

Also, http://jan.kneschke.de/projects/mysql/order-by-rand/ Do not use ORDER BY RAND(). It does not scale.

Upvotes: 0

Related Questions