1

I have two tables: agencies and lessons.

  • An agency can have many lessons.
  • A lesson belongs to one and only one agency.
  • A lesson has a date.

I want to get the 10 agencies (or just their ID) having the most recent lessons. In other words I want agencies ordered by their lessons limited to 10 agencies.

I tried various queries with order by and group by but I'm stuck. Everything I try to write is absurd. Here is the first thing I tried:

SELECT
  agency_id
FROM lessons
GROUP BY agency_id
ORDER BY start_at;

It fails because:

column "lessons.start_at" must appear in the GROUP BY clause or be used in an aggregate function

I think I understand why. But I'm lost, I don't know how to fix this query. Where should I start?

rap-2-h
  • 30,204
  • 37
  • 167
  • 263

1 Answers1

4

The order by and select list items must either be part of the group by clause or aggregate functions. You could sort according to the maximum (i.e., last) lesson date:

SELECT   agency_id
FROM     lessons
GROUP BY agency_id
ORDER BY MAX(start_at) DESC
LIMIT    10
Mureinik
  • 297,002
  • 52
  • 306
  • 350