Mysql: order by maximum N values ​​from subquery

Question

I'm going to throw a towel with this.

Preface: I want to make this work with any N, but for simplicity I set N to 3.

I have a query (in particular, MySQL) that should retrieve data from a table and sort based on the top three values ​​from this table and then refer to other sorting criteria.

So basically I have something like this:

SELECT tbl.id FROM tbl1 AS maintable LEFT JOIN tbl2 AS othertable ON maintable.id = othertable.id ORDER BY othertable.timestamp DESC, maintable.timestamp DESC 

This is all a basic tutorial. But the problem is that I need the first ORDER BY clause to get only the three largest values ​​in othertable.timestamp, and then return to maintable.timestamp.

In addition, executing the LIMIT 3 subquery for other tables and joining it is not necessary, since this should work with an arbitrary number of WHERE conditions applied to the main table.

I was almost able to get it to work with this approach based on user variables, but it fails because it does not take into account ordering, so it will find FIRST three other table values:

 ORDER BY ( IF(othertable.timestamp IS NULL, 0, IF( (@rank: =@rank +1) > 3, null, othertable.timestamp ) ) ) DESC 

(with @rank: = 0 preceding the statement)

So ... any advice on this? I'm going crazy with this problem. Another parameter that I have for this is that since I am only modifying an existing (significantly complex) request, I cannot make an external external request. In addition, as already noted, I am in MySQL, so any solutions using the ROW_NUMBER function are unfortunately not available.

Thanks to everyone in advance.

EDIT. Here are some examples of timestamped data that are suppressed to prime integers to illustrate what I need:

 maintable id timestamp 1 100 2 200 3 300 4 400 5 500 6 600 othertable id timestamp 4 250 5 350 3 550 1 700 => 1 3 5 6 4 2 

And if for some reason we add WHERE NOT maintable.id = 5 to the query, this is what we should get:

 1 3 4 6 2 

... because now 4 is among the top 3 values ​​in another table, referring to this set.

So, as you can see, the row with id 4 from another table is not included in the order, since it is the fourth in descending order of timestamp values, so it returns to ordering by the base timestamp.

The real world’s need for this is as follows: I have the content in the “maintable”, and “othertable” is basically a marker for the contained content marked “assigned date”. I have an idea where I should float the last 3 recognized elements at the top, and the rest of the list is a reverse chronological list.

Answer 1

Maybe something like this.

 SELECT id FROM (SELECT tbl.id, CASE WHEN othertable.timestamp IS NULL THEN 0 ELSE @i := @i + 1 END AS num, othertable.timestamp as othertimestamp, maintable.timestamp as maintimestamp FROM tbl1 AS maintable CROSS JOIN (select @i := 0) i LEFT JOIN tbl2 AS othertable ON maintable.id = othertable.id ORDER BY othertable.timestamp DESC) t ORDER BY CASE WHEN num > 0 AND num <= 3 THEN othertimestamp ELSE maintimestamp END DESC 

Answer 2

Modified Answer:

 select ilv.* from (select sq.*, @i: =@i +1 rn from (select @i := 0) i CROSS JOIN (select m.*, o.id o_id, o.timestamp o_t from maintable m left join othertable o on m.id = o.id where 1=1 order by o.timestamp desc) sq ) ilv order by case when o_t is not null and rn <=3 then rn else 4 end, timestamp desc 

SQLFiddle is here .

Change the where 1=1 condition inside the sq subquery to meet the required complex selection conditions and add the appropriate limit criteria after the final order by for the search call requirements.

Answer 3

Can I use a join query as shown below?

 (SELECT id,timestamp,1 AS isFeatured FROM tbl2 ORDER BY timestamp DESC LIMIT 3) UNION ALL (SELECT id,timestamp,2 AS isFeatured FROM tbl1 WHERE NOT id in (SELECT id from tbl2 ORDER BY timestamp DESC LIMIT 3)) ORDER BY isFeatured,timestamp DESC 

This may be somewhat redundant, but semantically closer to the question you ask. It will also allow you to parameterize the number of recognized results that you want to return.