0

[SQL Challenge] Unique movie names

I have a table 'movies' with the following table data. id name release_year ------------------------------------------ 1 Titanic 1997 2 The Avengers 1998 3 The Avengers 2012 4 Twilight 1998 5 Twilight 2008 'id' is the primary key, names can be same. You can also assume (name, year) is unique. I want to see the following output name -------- The Avengers (1998) The Avengers (2012) Twilight (1998) Twilight (2008) Titanic If the title is unique, it should be displayed as it is, if there are multiple movies with same name but with different release years, they should have them in parenthesis.

28th Dec 2017, 1:43 PM
Ravi Chandra Enaganti
Ravi Chandra Enaganti - avatar
2 Respuestas
+ 1
Something like that works in MySQL: SELECT name FROM movies GROUP BY name having COUNT(name) = 1 UNION SELECT CONCAT(movies.name, ' (', movies.release_year, ')') FROM movies INNER JOIN (SELECT name FROM movies GROUP BY name HAVING COUNT(id) > 1) dup ON movies.name = dup.name
28th Dec 2017, 2:08 PM
thex
thex - avatar
+ 1
I also had a similar answer. select concat(name,' (',release_year,')') as name from movies where name in (select name from movies group by name having count(id) > 1) union select name from movies where name in (select name from movies group by name having count(id) = 1);
2nd Jan 2018, 11:38 AM
Ravi Chandra Enaganti
Ravi Chandra Enaganti - avatar