CODEWITHSUNDEEP
mysqlsql
lvil
lvil

Reputation: 4336

Selecting with UNION but limiting every subquery and receiving distinct values

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?

Upvotes: 8

Views: 3132

Answers (4)

David
David

Reputation: 1071

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.

Upvotes: 0

Jocelyn
Jocelyn

Reputation: 11413

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.

Upvotes: 3

Swathi
Swathi

Reputation: 157

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

Upvotes: -1

aurora
aurora

Reputation: 9627

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

Upvotes: 0

Related Questions