Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
12.3.2
-
Windows 11
-
Not for Release Notes
Description
DROP TABLE if EXISTS WORK.MyTVShows ;
CREATE TABLE WORK.MyTVShows (tconst CHAR(10)) ;
INSERT INTO WORK.MyTVShows (tconst) VALUES ('tt0898266') ;
DROP TABLE if EXISTS WORK.Episodes ;
CREATE TABLE WORK.Episodes (tconst CHAR(10), parentTconst CHAR(10)) ;
INSERT INTO WORK.Episodes (tconst, parentTconst) VALUES ('tt0775431', 'tt0898266'), ('tt1091289', 'tt0898266') ;
DROP TABLE if EXISTS WORK.ActingRoles ;
CREATE TABLE WORK.ActingRoles (tconst CHAR(10), nconst CHAR(10), voice CHAR(1)) ;
INSERT INTO WORK.ActingRoles (tconst, nconst, voice) VALUES ('tt0775431', 'nm0192505', ' '), ('tt1091289', 'nm0192505', ' ') ;
DROP TABLE if EXISTS WORK.Artists ;
CREATE TABLE WORK.Artists (nconst CHAR(10), primaryName VARCHAR(128), gender CHAR(1)) ;
INSERT INTO WORK.Artists (nconst, primaryName, gender) VALUES ('nm0192505', 'Kaley Cuoco', 'F') ;
- Distinct + Having ==> Empty Result
SELECT d.nconst, d.primaryName,
COUNT(b.tconst) AS nbrEpisode,
COUNT(distinct b.parentTconst) AS nbrShow,
RANK() OVER (ORDER BY nbrEpisode desc) as actRank
FROM WORK.MyTVShows a,
WORK.Episodes b,
WORK.ActingRoles c,
WORK.Artists d
WHERE a.tconst = b.parentTconst
AND b.tconst = c.tconst
AND c.nconst = d.nconst
AND c.voice NOT IN ("C", "D")
AND d.gender = "F"
GROUP BY d.nconst, d.primaryName
HAVING nbrEpisode >= 1
ORDER BY actRank, d.primaryName ;
- Distinct without Having ==> Works, but I want Having clause
SELECT d.nconst, d.primaryName,
COUNT(b.tconst) AS nbrEpisode,
COUNT(distinct b.parentTconst) AS nbrShow,
RANK() OVER (ORDER BY nbrEpisode desc) as actRank
FROM WORK.MyTVShows a,
WORK.Episodes b,
WORK.ActingRoles c,
WORK.Artists d
WHERE a.tconst = b.parentTconst
AND b.tconst = c.tconst
AND c.nconst = d.nconst
AND c.voice NOT IN ("C", "D")
AND d.gender = "F"
GROUP BY d.nconst, d.primaryName
ORDER BY actRank, d.primaryName ;
- Having without Distinct ==> Works, but I need Distinct to obtain desired result
SELECT d.nconst, d.primaryName,
COUNT(b.tconst) AS nbrEpisode,
COUNT(b.parentTconst) AS nbrShow,
RANK() OVER (ORDER BY nbrEpisode desc) as actRank
FROM WORK.MyTVShows a,
WORK.Episodes b,
WORK.ActingRoles c,
WORK.Artists d
WHERE a.tconst = b.parentTconst
AND b.tconst = c.tconst
AND c.nconst = d.nconst
AND c.voice NOT IN ("C", "D")
AND d.gender = "F"
GROUP BY d.nconst, d.primaryName
HAVING nbrEpisode >= 1
ORDER BY actRank, d.primaryName ;
Attachments
Issue Links
- duplicates
-
MDEV-40884 Materializing a grouped input changes the result of a window query with `HAVING SUM(DISTINCT ...)`
-
- Confirmed
-