MySQL Order by Rand(), Name Asc

MySQL ORDER BY rand(), name ASC

Use a subquery:

SELECT * FROM 
(
    SELECT * FROM users ORDER BY rand() LIMIT 20
) T1
ORDER BY name 

The inner query selects 20 users at random and the outer query orders the selected users by name.

SQL Sort by RAND and then sort by something else

Answer here might help you out

SELECT * FROM 
(
    SELECT * FROM table_name ORDER BY rand() LIMIT 6
) T1
ORDER BY `parts`.price

Basically you're creating a temporary table with the first query and then sorting it.

MySQL order by two column, one ASC and one RAND()

This is your order by:

ORDER BY prioritet ASC, id ASC, RAND()

It is ordering by prioiritet and then by id. You want:

ORDER BY prioritet ASC, RAND()

In other words, don't order by id at all.

This assumes that id is unique on each row (which is reasonable given the name). If ids are not unique, you need a more complex query:

SELECT a.id, a.user_id, a.active, a.prioritet
FROM agents a join
     (select id, rand() as r
      from (select distinct id from agents) a
     ) randid
     on a.id = randid.id
WHERE user_id = ' + user_id + ' AND active = \'1\'
ORDER BY a.prioritet ASC, randid.r, a.id;

That is, you need to assign a random value to each id, join it back in, and order appropriately. You only need this if the assumption that id is unique is not true.

how to fetch random stores name but price in ascending order from database?

Try this:

Select *, RAND() as r from `food` where `veg_non` = 'Veg'
    and `food_price` <= ? ORDER BY `food_price` ASC, r ASC

Order by 2 columns and rand()

Yes, you should be able to achieve the specified resultset in a single query. (If we're understanding the specification; there appears to be a large degree of confusion as to the actual spec.)

If vip column is integer, and contains values 0 and 1, and the "VIP" rows are the rows with vip=1, and you want those rows returned first (before the "non-VIP" rows, but you want those returned in a pseudo-"random" order, followed by the "non-VIP" rows (vip=0) in order by price

One way to achieve that would be use an ORDER BY clause like this:

SELECT n.*
  FROM notes n
 ORDER BY IF(n.vip, RAND(), NULL), n.price

NOTE: replace n.price with n.id or n.name to order "by id" or "by name", respectively, instead of "by price".

Sorting by some column and also by rand() in MySQL

What you are doing is valid - it will order the results in descending order by a but randomize the order of ties.

However to do what you want you need to first use a subquery to get the latest 100 records and then afterwards sort the results of that subquery randomly using an outer query:

SELECT * FROM
(
    SELECT * FROM table1
    ORDER BY date DESC
    LIMIT 100
) T1
ORDER BY RAND()

SQL Query with Group , Order by and Random at the same time

This query:

select Nombre, rand() rnd 
from test 
group by Nombre

returns a random number for each unique name in the table.

Join it to the table and sort first by that random number and then by Orden:

select t.* 
from test t 
inner join (select Nombre, rand() rnd from test group by Nombre) r
on r.Nombre = t.Nombre
order by r.rnd, t.Orden

See the demo.

Results:

> Id | Nombre  | Orden
> -: | :------ | ----:
>  7 | William |  null
>  1 | Mark    |  null
>  2 | David   |     1
>  4 | David   |     2
>  3 | John    |     1
>  6 | John    |     2
>  5 | John    |     3

> Id | Nombre  | Orden
> -: | :------ | ----:
>  2 | David   |     1
>  4 | David   |     2
>  3 | John    |     1
>  6 | John    |     2
>  5 | John    |     3
>  1 | Mark    |  null
>  7 | William |  null

> Id | Nombre  | Orden
> -: | :------ | ----:
>  2 | David   |     1
>  4 | David   |     2
>  1 | Mark    |  null
>  7 | William |  null
>  3 | John    |     1
>  6 | John    |     2
>  5 | John    |     3

PHP/Mysql Order by and Rand()

You could give each result a random number and order the result first by points DESC then by the random value:

SELECT *, RAND() random FROM users ORDER BY points DESC, random ASC

Probably there's also the solution with:

SELECT * FROM users ORDER BY points DESC, RAND()

But I'm not sure about that one.

How to: MySQL order by user_id (RAND) with pagination

The RAND() function does not really generate random numbers but what's called pseudo random numbers: numbers are calculated with a deterministic formula and they're just intended to look random. To calculate a new number, you take the previous one and apply the formula to it, and that's how we get different output with a deterministic function: by using different input.

The initial number we use is known as seed. If you have a look at the manual you'll see that RAND() has an optional argument:

RAND(), RAND(N)

Returns a random floating-point value v in the range 0 <= v < 1.0. If
a constant integer argument N is specified, it is used as the seed
value, which produces a repeatable sequence of column values

You've probably figured out by now where I want to go:

mysql> SELECT language_id, name FROM language ORDER BY RAND(33);
+-------------+----------+
| language_id | name     |
+-------------+----------+
|           3 | Japanese |
|           1 | English  |
|           4 | Mandarin |
|           6 | German   |
|           5 | French   |
|           2 | Italian  |
+-------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT language_id, name FROM language ORDER BY RAND(33);
+-------------+----------+
| language_id | name     |
+-------------+----------+
|           3 | Japanese |
|           1 | English  |
|           4 | Mandarin |
|           6 | German   |
|           5 | French   |
|           2 | Italian  |
+-------------+----------+
6 rows in set (0.00 sec)

P.S. The manual is not explicit about the seed range (it just says integer), you might need some extra research (or just some quick testing).

---

Related Topics