Please hep with join/group by problem

[bruceyeah]

Hi, hoping someone can help me design a query that achieves this, I'm getting really stuck.

I have two tables that I need to join:

 

Series

- seriesId

 

Episode

- id

- seriesId

- date

 

Firstly, I need the result set to be ordered by seriesId, so that all the episodes in the same series are adjacent in the result.

However, I also need to sort the series by most recent date, in that the series with the most recent episode appears at the top of the result.

 

 

So the result would be:

 

series

episode

age

B

6

2 days

B

4

4 days

B

3

6 days

A

5

3 days

A

2

5 days

A

1

7 days

 

How would this be achieved? I've tied all manner of joins and group by by I can't get it to sort the series and then episodes in this way.

 

And before anyone asks why I'm not just sorting in PHP ... I'm trying to be ultra efficient and only return one row at a time to my View so as not to pass around big arrays in my application.

[gizmola]

You have 2 options that I can see.  The first is to change your Series table so that it includes a "LastAirDate" column that has the date of the most recently aired episode for that series.  This could be kept up to date with a trigger or with your frontend code, whenever you do an INSERT into Episode. 

 

At that point, you simply join Series to Episode on seriesId and order by LastAirDate, seriesId, id.

 

You could also create a View that in essence creates this column.  Based on the names you provided:

 

CREATE VIEW series_view as SELECT s.seriesId, MAX(date) as lastAirDate FROM Series s JOIN (Episode e) ON (e.seriesId = s.SeriesId) GROUP BY s.seriesId ORDER BY MAX(date) desc;

 

 

Once the view exists, it's just like a table, so you can join Episode to it, and do the order by you need for the result set you wanted

 

SELECT v.seriesId, v.lastAirDate, e.id, e.date as aired FROM series_view v JOIN (Episode e) ON (e.seriesId = v.seriesId) ORDER BY lastAirDate desc, e.seriesId, e.id;

 

 

[mikosiko]

or calculate the "age" in the SELECT directly and order by seriesId, calculated_age ASC  (if I did understand correctly your objective)

 

as in :

 

  SELECT a.seriesId AS serienmr
               b.id AS episode,
               DATEDIFF(curdate(), b.created) AS age
      FROM series AS s
          JOIN episode ON a.seriesId = b.seriesId
     ORDER by seriesnmr, age ASC

[mikosiko]

Humm... I just read your post again.... the solution that I gave below doesn't solve your objective completely... I misread your post.... Gizmola VIEW approach solve it.