CODEWITHSUNDEEP
mysqlgroup-byconditional-statements
Parapluie
Parapluie

Reputation: 736

GROUP BY in mysql conditional on IS NULL column

I am having a terrible time figuring out the proper way to use GROUP BY to eliminate redundant rows in these results:

id  title               author  city        year    pageId  page
1   The Faraway Tree    Blyton  New York    1951    NULL    1
1   The Faraway Tree    Blyton  New York    1951    NULL    2
1   The Faraway Tree    Blyton  New York    1951    NULL    3
1   The Faraway Tree    Blyton  New York    1951    NULL    4
1   The Faraway Tree    Blyton  New York    1951    NULL    5
1   The Faraway Tree    Blyton  New York    1951    NULL    7
1   The Faraway Tree    Blyton  New York    1951    NULL    9
1   The Faraway Tree    Blyton  New York    1951    NULL    13
1   The Faraway Tree    Blyton  New York    1951    NULL    30
2   Winnie the Pooh     Milne   London      1937    NULL    76
2   Winnie the Pooh     Milne   London      1937    NULL    73
2   Winnie the Pooh     Milne   London      1937    NULL    74
2   Winnie the Pooh     Milne   London      1937    NULL    75
1   The Faraway Tree    Blyton  New York    1951    4       32
2   Winnie the Pooh     Milne   London      1937    6       74

All that I want to do is simply eliminate duplicates, but ONLY if pageId IS NULL.

id  title               author  city        year    pageId  page
1   The Faraway Tree    Blyton  New York    1951    NULL    1
2   Winnie the Pooh     Milne   London      1937    NULL    76
1   The Faraway Tree    Blyton  New York    1951    4       32
2   Winnie the Pooh     Milne   London      1937    6       74

The logic, in plain English, would be, IF pageId IS NULL, THEN GROUP BY title, ELSE keep the single row separate.

It sounds simple enough, but no matter what aggregate function or GROUP BY modifier that I use, I either get a syntax error, or an incorrect result (usually a row with a pageId getting grouped with NULL pageIds).

Note that when pageId is returned as NULL, the column "page" becomes unimportant to me.

Here is the SQL query (MySQL v. 5.6.37) that I am using:

SELECT id,title,author,city,`year`,NULL as pageId,pageNum as page 
FROM titles
WHERE id IN ([complex subquery])
UNION ALL (
    SELECT id,title,author,city,`year`,pageId,pageNum 
    FROM titles 
    WHERE titles.t_id IN ([complex subquery])
    )

I've plugged away at this for three days, and can not find an accurate solution on SO or the Web. I reckon it's time to humbly ask for your help. Please.

Upvotes: 2

Views: 1702

Answers (3)

Jarek Jóźwik
Jarek Jóźwik

Reputation: 77

What if you use IFNULL(pageId, 0) and then use this pseudo column in groupd by? Like:
SELECT id,title,author,city,year,IFNULL(pageId, 0) as pageId, pageNum as page FROM titles WHERE id IN ([complex subquery]) group by title, pageId;
You can put any value instead of 0 in IFNULL - 'none' would do as well.

Upvotes: 0

Yu-Lin Chen
Yu-Lin Chen

Reputation: 559

A little bit tricky by converting null pageId to 0, then group by it:

select
    case when pageId is null then 0 else pageId end,
    id,title,author,city,`year`, page
from titles
group by 1, id,title,author,city,`year`, page

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Something like this should work:

SELECT id, title, author, city, `year`, NULL AS pageId, MIN(page) as page 
FROM title
WHERE pageId IS NULL
GROUP BY id, title, author, city, `year`

UNION ALL

SELECT id, title, author, city, `year`, pageId, page 
FROM title
WHERE pageId IS NOT NULL;

Demo here

Upvotes: 2

Related Questions