Is it possible to combine into SELECT in SQL?

Question

Let's say I want to get 100 records from a table called messages , and I want to get them as follows:

 1st message 100th message 2nd message 99th message 3rd message 98th message (...) 

Is there any way to do this efficiently? What would be the appropriate request? Or should I make a query to select the first 50, a query to select the last 50, and then combine the results?

Answer 1

Try if your identifier is a sequence of numbers:

First

 SET @half = (SELECT MAX(id) FROM messages)/2; 

Then

 SELECT * FROM `messages` ORDER BY (IF(id<@half,@half*2-id,id-1)) DESC,id ASC; 

Answer 2

The bottom line is to create two virtual columns "serie_order" (option) and "serie" (constant), which you will use on both parts of your data (you will have to split your data into two parts).

 SELECT * FROM ( SELECT 1 as serie, message_id AS serie_order , * FROM (SELECT message_id FROM messages ) as part_up UNION SELECT 2 as serie, 101-message_id as serie_order, * FROM (SELECT message_id FROM messages) as part_down ) AS world ORDER BY serie_order ASC, serie ASC LIMIT 100 

Answer 3

 set @rank:=0; select id from (select id, @rank:=(coalesce(@rank, 0)+1) as new_order from a_table order by some_column limit 100) as ordering order by if (new_order<=50, new_order-1, abs(100-new_order)) asc;