sql - aggregate max function and group by

Question

select city, title, releasedate
from movies join shownat on shownat.movietitle = movies.title join theatres on theatres.theatrename = shownat.theatrename
group by city, title, releasedate
order by city, max(releasedate) desc;

Above is my query, and this problem to solve.

Find the titles of the newest movies shown in each city. Display the city name and the newest movie title ordered by city name and movie title.

The normalized data has 3 tables called theatres, shownat and movies. Yes, I realize the release date could be more efficient on the shownat table, and to me it makes more sense because not always are videos always released in same areas or cities at the same date. However it is my last homework problem that I am stuck on. What is needed is that the city should only have 1 listing. I thought that the group by city would take care of this. For each city I need the title of the movie that has the newest release date in each of the particular cities(4) in the data set. I am unsure as to why I am getting duplicates here when I have the group by feature for this and its use to handle the aggregate max function. The max function should just give me the newest release yes?

"CITY"          "TITLE"        "RELEASEDATE"
"Cincinnati"    "Interstellar"  07-NOV-14
"Cincinnati"    "Big Hero 6"    07-NOV-14
"Cincinnati"    "Nightcrawler"  31-OCT-14
"Cincinnati"    "Gone Girl"     03-OCT-14
"Cincinnati"    "The Pianist"   03-JAN-03
"Cincinnati"    "Fargo" 05-APR-96
"Cincinnati"    "Schindler's List"  04-FEB-94
"Florence"      "Big Hero 6"    07-NOV-14
"Florence"      "Interstellar"  07-NOV-14
"Florence"      "Nightcrawler"  31-OCT-14
"Florence"  "Gone Girl" 03-OCT-14
"Florence"  "District 9"    14-AUG-09
"Florence"  "A Perfect Getaway" 07-AUG-09
"Florence"  "Aliens in the Attic"   31-JUL-09
"Florence"  "Away We Go"    26-JUN-09
"Florence"  "Up"    29-MAY-09
"Florence"  "Star Trek" 08-MAY-09
"Florence"  "The Hurt Locker"   10-OCT-08
"Florence"  "The Dark Knight"   18-JUL-08
"Florence"  "The Departed"  06-OCT-06
"Florence"  "The Green Mile"    10-DEC-99
"Newport"   "Interstellar"  07-NOV-14
"Newport"   "Big Hero 6"    07-NOV-14
"Newport"   "Gone Girl" 03-OCT-14
"Newport"   "District 9"    14-AUG-09
"Newport"   "A Perfect Getaway" 07-AUG-09
"Newport"   "Away We Go"    26-JUN-09
"Newport"   "Up"    29-MAY-09
"Newport"   "The Departed"  06-OCT-06
"Wilder"    "Big Hero 6"    07-NOV-14
"Wilder"    "Interstellar"  07-NOV-14
"Wilder"    "Gone Girl" 03-OCT-14
"Wilder"    "Public Enemies"    01-JUL-09
"Wilder"    "The Departed"  06-OCT-06

But it still gives me multiples in the cities. I realize distinct could take care of this, but I thought group by would as well? — camdixon
– camdixon, Commented Jan 29, 2016 at 9:06
Each city only once, return the row(s) with latest date? Either NOT EXISTS, or a self join! — jarlh
– jarlh, Commented Jan 29, 2016 at 9:07

Ullas · Accepted Answer · 2016-01-29 09:23:52Z

1

This would be the query but I'm not quite sure if max function would works with a date variable:

SELECT city, title, max (releasedate) as max_dateRelease
FROM movies inner join shownat on shownat.movietitle = movies.title join theatres 
on theatres.theatrename = shownat.theatrename
GROUP BY city, title, releasedate
ORDER BY city, max_dateRelease desc

edited Jan 29, 2016 at 9:23

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

answered Jan 29, 2016 at 9:09

Jessica

1115 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rick James Over a year ago

Don't GROUP BY something that you are aggregating over (releasedate).

camdixon Over a year ago

I made an edit to the answer removing the releasedate from group by and accepted as the official answer.

Vahid Heydarinezhad · Accepted Answer · 2016-01-29 11:12:01Z

0

select T1.city,T2.movietitle,T1.releasedate
from
(select city,max(releasedate) as maxreleasedate
from movies join shownat on shownat.movietitle = movies.title join theatres on theatres.theatrename = shownat.theatrename
group by city) as T1
inner join
(select shownat.movietitle,shownat.city,shownat.releasedate)
from shownat) as T2
 on T1.city=T2.city and T1.maxreleasedate=T2.releasedate

This is one way and isn't best way.

answered Jan 29, 2016 at 11:12

Vahid Heydarinezhad

1264 bronze badges

Collectives™ on Stack Overflow

sql - aggregate max function and group by

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related