Selection with UNION, but restricting each subquery and getting individual values

Question

I have a table and you want to get 15 values ​​with one order and 15 with a different order. The goal is to get exactly 30 different values.
This is my code:

(SELECT * FROM table1 WHERE criteria ORDER BY views DESC LIMIT 15) UNION All (SELECT * FROM table1 WHERE criteria ORDER BY date_upload DESC LIMIT 15) 

I know how to accomplish a task with two queries (with NOT IN), but is there a way to do this in a single query?

Answer 1

If necessary, replace "id" with the name of your primary key:

 (SELECT * FROM table1 WHERE criteria ORDER BY views DESC LIMIT 15) UNION (SELECT * FROM table1 WHERE criteria AND id NOT IN(SELECT id FROM table1 WHERE criteria LIMIT 15) ORDER BY date_upload DESC LIMIT 15) 

This request:
- selects the top 15 entries matching the criteria sorted by views
- selects the top 15 matching criteria, not the first SELECT, and orders their date_upload

With this query you are sure to get 30 records each time 30 different records are available in the table.

Answer 2

I'm not quite sure if this is what you are looking for, but you can always wrap this in a subquery and use DISTINCT in your external SELECT to filter the results. Of course, there is no guarantee that you will get 30 search results back:

 SELECT DISTINCT * FROM ( (SELECT * FROM table1 WHERE criteria ORDER BY views DESC LIMIT 15) UNION All (SELECT * FROM table1 WHERE criteria ORDER BY date_upload DESC LIMIT 15) ) AS a 

You can set a higher limit for your subselects and add an extra limit for your outer selection, though ...

Answer 3

UNION ALL lists duplicate records, rather than interfering with Distinct and UNION ALL. Try "UNION", which gives you a great value.

Try it!

SELECT top 15 * FROM table1 UNION SELECT top 15 * FROM table1 ORDER BY date_upload

Answer 4

 WITH combined AS ( SELECT * from table1 union SELECT * from table2 ) SELECT TOP 30 * FROM combined ORDER BY date_uploaded 

NOTE. It is not recommended to use * with UNION. It is better to list the fields.