Selecting with UNION but limiting every subquery and receiving distinct values
mysql, sql
Solution
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 query: - selects the top 15 records matching criteria ordered by views - selects the top 15 matching criteria and not in the first SELECT, and orders them by date_upload
With this query you will be sure to get 30 records every time 30 distinct records are available in table1.
Problem
I have a table and want to get 15 values with one order and 15 with another order. The aim is getting exactly 30 distinct 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 complete the task with two queries ( with NOT IN ), but is there a way to make it in one query?