Is it possible to divide records in both ascending and descending order in MySQL and display them alternatively?

Yes, you can perform this in MySQL by first getting the middle value. Let us first create a table:

mysql> create table DemoTable
(
   UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY
);
Query OK, 0 rows affected (0.65 sec)

Following is the query to insert some records in the table using insert command:

mysql> insert into DemoTable values();
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.06 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.10 sec)

Following is the query to display records from the table using select command:

mysql> select *from DemoTable;

This will produce the following output

+--------+
| UserId |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
|      7 |
|      8 |
|      9 |
|     10 |
+--------+
10 rows in set (0.00 sec)

Here is the query to get the middle value first:

mysql> set @middleValue=(select max(UserId) from DemoTable)/2;
Query OK, 0 rows affected (0.01 sec)

Now, let us get the ascending and descending order value alternatively:

mysql> select *from DemoTable ORDER BY (IF(UserId <@middleValue,@middleValue*2-
UserId,UserId-1)) DESC,UserId ASC;

This will produce the following output

+--------+
| UserId |
+--------+
|      1 |
|      10|
|      2 |
|      9 |
|      3 |
|      8 |
|      4 |
|      7 |
|      6 |
|      5 |
+--------+
10 rows in set (0.00 sec)

Krantik Chavan

Published on 26-Apr-2019 12:15:59

• Related Questions & Answers
• Order MySQL records randomly and display name in Ascending order
• Is it possible to sort varchar data in ascending order that have both string and number values with MySQL?
• MySQL query to first set negative value in descending order and then positive value in ascending order
• MySQL order by 0 first and then display the record in descending order?
• Program to merge intervals and sort them in ascending order in Python
• MySQL Order by a specific column x and display remaining values in ascending order
• Program to find overlapping intervals and return them in ascending order in Python
• Select last 20 records ordered in ascending order in MySQL?
• How to ORDER BY DESC and display the first 3 records in MySQL?
• Recursion example in JavaScript to display numbers is descending order?
• MySQL ORDER BY with different ordering for some of the values as descending and others ascending?
• ORDER BY rand() and keep them grouped in MySQL?
• Check if it is possible to rearrange rectangles in a non-ascending order of breadths in Python
• Java Program to find keys from both the Linked HashMap and store it in a list alternatively
• Find and display duplicate records in MySQL?