Let us say I wish to retrieve 100 records from the table known as
messages, and I wish to obtain them the next way:
1st message 100th message 2nd message 99th message 3rd message 98th message (...)
Can there be any method of doing this effectively? What will be the appropriate query? Or must i create a query to choose the very first 50, a question to choose the final 50 after which merge the outcomes?
Try in case your ID is really a sequence of amounts:
SET @half = (SELECT MAX(id) FROM messages)/2;
SELECT * FROM `messages` ORDER BY (IF(id<@half,@half*2-id,id-1)) DESC,id ASC;
The thing is to produce two virtual posts "serie_order" (variant) and "serie" (constant) you will employ on areas of your computer data (you will need to split your computer data in 2).
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
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;