Select Rows with Randomized Initial Ones and Ordered by Criteria in MySQL

For this, you can use ORDER BY CASE statement. Let us create a table −

mysql> create table DemoTable1926
   (
   Position varchar(20),
   Number int
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1926 values('Highest',50);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1926 values('Highest',30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1926 values('Lowest',100);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1926 values('Lowest',120);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1926 values('Lowest',90);
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1926;

This will produce the following output −

+----------+--------+
| Position | Number |
+----------+--------+
| Highest  |     50 |
| Highest  |     30 |
| Lowest   |    100 |
| Lowest   |    120 |
| Lowest   |     90 |
+----------+--------+
5 rows in set (0.00 sec)

Here is the query to select rows if initial ones are randomized and rest ordered by criteria −

mysql> select * from DemoTable1926
   order by Position desc,case Position when 'Highest' then rand()
   else Number end asc;

This will produce the following output −

+----------+--------+
| Position | Number |
+----------+--------+
| Lowest   |     90 |
| Lowest   |    100 |
| Lowest   |    120 |
| Highest  |     50 |
| Highest  |     30 |
+----------+--------+
5 rows in set (0.00 sec)