0

I have a table(a temporary table which I've created from a long SQL query) in the following format:

Moviename | Actorname | Actorage
-----------------------------------
m1 | a1 | 5

m1 | a2 | 10

m1 | a3 | 15

m2 | a1 | 10

m2 | a4 | 20

m3 | a5 | 35

I would like to find this:

For every movie in my temporary table, I want to find the oldest actor. However, the aggregation won't let me.

What I've tried so far:

Select
 Moviename,
 Actorname,
 Max(Actorage)
From TempTable
Group By Moviename

The SQL query above tells me that I need to have Actorname in the group by statement. I know that for SQL to compile and execute the code, apart from the aggregation, all attributes must be grouped by.

I want the following result:

m1 | a3 | 15
m2 | a4 | 20
m3 | a5 | 35
o10575258
  • 3
  • 1
  • So yes what is the problem compiler is telling you to add actor name because you are selecting 3 columns 2 have been addressed and one not that is actor name – ThinkTank Mar 03 '19 at 12:44
  • 1
    Possible duplicate of [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) – Nick Mar 03 '19 at 12:45
  • @ThinkTank, I'm not complaining about the compiler. I'm asking what is the correct way to retrieve the info I'm looking for. I explained that I have understood why I need to add 2nd group by – o10575258 Mar 03 '19 at 13:13

0 Answers0