CODEWITHSUNDEEP
mysqlsqldatabaseselectmerge
federicot
federicot

Reputation: 12341

Is it possible to merge in a SELECT in SQL?

Let's say I want to retrieve 100 records from a table called messages, and I want to obtain them the following way:

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 query? Or should I make a query to select the first 50, a query to select the last 50 and then merge the results?

Upvotes: 6

Views: 202

Answers (3)

ajreal
ajreal

Reputation: 47321

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;

Upvotes: 0

131
131

Reputation: 3361

The point is to create two virtual columns "serie_order" (variant) and "serie" (constant) you'll use on both parts of your data (you'll have to split your data in two).

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

Upvotes: 0

Paulo H.
Paulo H.

Reputation: 1258

Try if your ID 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;

Upvotes: 2

Related Questions