Selecting with UNION but limiting every subquery and receiving distinct values

Question

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?

• eggyal: UNION ALL won't necessarily give you 30 distinct values (each of the two queries could include the same record); use instead UNION DISTINCT (or omit DISTINCT since that's the default). To limit to exactly 30 results you must first decide from which of the two queries to obtain an additional record should the top 15 of each intersect.
• Bridge: You say distinct values but you're returning all the columns in table1. Is there only one column?
• Lukas Eder: What happens if both subselects return the same record? Will you have 29 records as a result? Or will you have 30 records (from which table will you return an additional record?)
• D'Arcy Rittich: Technically, that is one query. It is just one query with two unioned select statements in it.
• lvil: I know this query is not correct. In fact it worked fine as "new" never intersected with "views", so I used it to get exactly 30 records. It doesn't matter meanwhile from which "SELECT" to take additional record.

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 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.

Answer 2

I am not quite sure, if it's what you are looking for, but you can always wrap this in a subselect and use a DISTINCT in your outer 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 could set a higher limit for your subselects and add an additional limit for your outer select, though ...

• lvil: I had this idea, though if I get 29 records, I will have to perform rather a heavy query with "NOT IN(id1,id2,...,id29)"
• aurora: maybe i did not fully understand the problem before. the problem is, that with a normal distinct / union (distinct) you won't get ever any results from your second select back, right?
• lvil: No, it is just that with distinct union i can get less that 30 records.

Answer 3

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

NOTE: it's not great idea to use the * with UNION. Better to list the fields.

• Mosty Mostacho: This is not SQL Server :s

Answer 4

UNION ALL will list out the duplicate records, Instead of messing up with Distinct and UNION ALL. Try with "UNION" which gives you the distinct value.

Try this!

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

• Barmar: MySQL doesn't have TOP 15.