CODEWITHSUNDEEP
mysqlsqlselectunion
mr.incredible
mr.incredible

Reputation: 4185

SQL how to union selections and exclude one?

At first I want to select first 5 rows with highest raiting.

SELECT
        e.id embed_id,
    e.type embed_type,
    e.embed,
    IF(ul.embed_id IS NULL,0,1) liked,
    (SELECT COUNT(*) FROM user_likes ula WHERE ula.embed_id = e.id) u_likes
FROM `embeds` e
LEFT JOIN user_likes ul ON ul.embed_id = e.id AND ul.user_id = 1
ORDER BY u_likes DESC
LIMIT 5

Then I want to select other rows ordering just by id, excluding first 5 rows selected before.

SELECT
    e.id embed_id,
    e.type embed_type,
    e.embed,
    IF(ul.embed_id IS NULL,0,1) liked,
    (SELECT COUNT(*) FROM user_likes ula WHERE ula.embed_id = e.id) u_likes
FROM `embeds` e
LEFT JOIN user_likes ul ON ul.embed_id = e.id AND ul.user_id = 1
ORDER BY embed_id
/* HOW TO EXCLUDE FIRTS SELECTION ? */

And then I want to union these two selections.

How to exclude first one from another and then union alltogether? Is it possible to use temporary table?

Also I've tried WHERE NOT EXISTS but it doesn't work:

SELECT
    e.id embed_id,
    e.type embed_type,
    e.embed,
    IF(ul.embed_id IS NULL,0,1) liked,
    (SELECT COUNT(*) FROM user_likes ula WHERE ula.embed_id = e.id) u_likes
FROM `embeds` e
LEFT JOIN user_likes ul ON ul.embed_id = e.id AND ul.user_id = 1
WHERE NOT EXISTS (
    SELECT
        e.id embed_id,
        (SELECT COUNT(*) FROM user_likes ula WHERE ula.embed_id = e.id) u_likes
    FROM `embeds` e
    LEFT JOIN user_likes ul ON ul.embed_id = e.id AND ul.user_id = 1
    ORDER BY u_likes DESC
    LIMIT 5
)

No rows for such query as result.

Any advice will be appreciated, thanks!

Upvotes: 1

Views: 1605

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31772

You can use OFFSET 5 with a huge LIMIT for your second query. Then reorder the UNION set:

(
    SELECT 1 as top5,
           e.id embed_id,
           ...
    FROM `embeds` e ...
    ORDER BY u_likes DESC 
    LIMIT 5
) UNION ALL (
    SELECT 0 as top5,
           e.id embed_id,
           ...
    FROM `embeds` e ...
    ORDER BY u_likes DESC
    LIMIT 99999999999999999999,
    OFFSET 5
)
ORDER BY top5 DESC, embed_id

Since there might be ties for u_likes, the inner ORDER BY clause will be non deterministic. You should append the primary key columns from both tables, to make the order deterministic. Something like

ORDER BY u_likes DESC, e.id, ula.id

I'm though not sure if your queries really do what you want, since you will probably get alot of duplicate rows.

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Can you please check this below query solve your purpose or not-

SELECT *,CASE WHEN (@rownum := @rownum + 1) < 6 THEN @rownum ELSE 6 END 
FROM
(
    SELECT
        e.id embed_id,
        e.type embed_type,
        e.embed,
        IF(ul.embed_id IS NULL,0,1) liked,
        (SELECT COUNT(*) FROM user_likes ula WHERE ula.embed_id = e.id) u_likes
    FROM `embeds` e
    LEFT JOIN user_likes ul ON ul.embed_id = e.id AND ul.user_id = 1
    ORDER BY (SELECT COUNT(*) FROM user_likes ula WHERE ula.embed_id = e.id) DESC
)B,
(SELECT @rownum:=0) AS foo
ORDER BY CASE WHEN (@rownum := @rownum + 1) < 6 THEN @rownum ELSE 6 END 
,embed_id

Upvotes: 1

Related Questions