Grouping and ordering

[miken32]

The manual says "MySQL extends the use of GROUP BY so that you can use
non-aggregated columns or calculations in the SELECT list that do not
appear in the GROUP BY clause... Do not use this feature if the columns
you omit from the GROUP BY part are not constant in the group. The
server is free to return any value from the group, so the results are
indeterminate unless all values are the same."

I'm looking for a way around that in MySQL (as opposed to having to do
it in PHP or something.) Is there one? An example is following:



I have a database of TV listings. Each show is assigned an identifier,
unique to that episode. I would like a list showing airings of show X,
but with duplicates removed. This I have done successfully, GROUPing
by the episode identifier. The only problem is that a random airing of
the episode is displayed; I'd like to have the first one displayed.

A very simplified example, this episode of '24' is on at 1am and 11am
on channel 28, 9pm on channel 46:

| episode | name | start | channel |
+---------+------+-------+---------+
| s04ep02 | 24 | 01:00 | 28 |
| s04ep02 | 24 | 11:00 | 28 |
| s04ep02 | 24 | 21:00 | 46 |

If I "SELECT start, channel FROM programs WHERE name='24' GROUP BY
episode" I will get a random selection from the list. Trying to use,
for example, MIN(start) does give me the first start time, but
everything else is still from a random row.

[Brian Wakem]

miken32 wrote:

> The manual says "MySQL extends the use of GROUP BY so that you can use
> non-aggregated columns or calculations in the SELECT list that do not
> appear in the GROUP BY clause... Do not use this feature if the columns
> you omit from the GROUP BY part are not constant in the group. The
> server is free to return any value from the group, so the results are
> indeterminate unless all values are the same."
>
> I'm looking for a way around that in MySQL (as opposed to having to do
> it in PHP or something.) Is there one? An example is following:
>
>
>
> I have a database of TV listings. Each show is assigned an identifier,
> unique to that episode. I would like a list showing airings of show X,
> but with duplicates removed. This I have done successfully, GROUPing
> by the episode identifier. The only problem is that a random airing of
> the episode is displayed; I'd like to have the first one displayed.
>
> A very simplified example, this episode of '24' is on at 1am and 11am
> on channel 28, 9pm on channel 46:
>
> | episode | name | start | channel |
> +---------+------+-------+---------+
> | s04ep02 | 24 | 01:00 | 28 |
> | s04ep02 | 24 | 11:00 | 28 |
> | s04ep02 | 24 | 21:00 | 46 |
>
> If I "SELECT start, channel FROM programs WHERE name='24' GROUP BY
> episode" I will get a random selection from the list. Trying to use,
> for example, MIN(start) does give me the first start time, but
> everything else is still from a random row.

How about:

SELECT start,channel FROM programs WHERE name = '24' AND episode = 's04ep02'
ORDER BY start ASC LIMIT 0,1;


--
Brian Wakem
Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]

[miken32]

Brian Wakem wrote:

> miken32 wrote:
>

> > The manual says "MySQL extends the use of GROUP BY so that you can use
> > non-aggregated columns or calculations in the SELECT list that do not
> > appear in the GROUP BY clause... Do not use this feature if the columns
> > you omit from the GROUP BY part are not constant in the group. The
> > server is free to return any value from the group, so the results are
> > indeterminate unless all values are the same."
> >
> > I'm looking for a way around that in MySQL (as opposed to having to do
> > it in PHP or something.) Is there one? An example is following:
> >
> >
> >
> > I have a database of TV listings. Each show is assigned an identifier,
> > unique to that episode. I would like a list showing airings of show X,
> > but with duplicates removed. This I have done successfully, GROUPing
> > by the episode identifier. The only problem is that a random airing of
> > the episode is displayed; I'd like to have the first one displayed.
> >
> > A very simplified example, this episode of '24' is on at 1am and 11am
> > on channel 28, 9pm on channel 46:
> >
> > | episode | name | start | channel |
> > +---------+------+-------+---------+
> > | s04ep02 | 24 | 01:00 | 28 |
> > | s04ep02 | 24 | 11:00 | 28 |
> > | s04ep02 | 24 | 21:00 | 46 |
> >
> > If I "SELECT start, channel FROM programs WHERE name='24' GROUP BY
> > episode" I will get a random selection from the list. Trying to use,
> > for example, MIN(start) does give me the first start time, but
> > everything else is still from a random row.

>
>
> How about:
>
> SELECT start,channel FROM programs WHERE name = '24' AND episode = 's04ep02'
> ORDER BY start ASC LIMIT 0,1;
>
>
> --
> Brian Wakem
> Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]

Thanks for the quick reply!

There are, using this example, many episodes of 24 on during the
reporting period, so I only want to search on the name, and show the
earliest instance of each episode.

[Bill Karwin]

miken32 wrote:

>> SELECT start,channel FROM programs WHERE name = '24' AND episode = 's04ep02'
>> ORDER BY start ASC LIMIT 0,1;
>>

> There are, using this example, many episodes of 24 on during the
> reporting period, so I only want to search on the name, and show the
> earliest instance of each episode.

SELECT p.*
FROM programs p LEFT JOIN programs p2
ON p.name = p2.name AND p.episode = p2.episode AND p.start > p2.start
WHERE p2.start IS NULL;

In other words, show programs for which there is no other showing of the
same episode with an earlier start time.

Regards,
Bill K.

[miken32]

Bill Karwin wrote:

> miken32 wrote:

> >> SELECT start,channel FROM programs WHERE name = '24' AND episode = 's04ep02'
> >> ORDER BY start ASC LIMIT 0,1;
> >>

> > There are, using this example, many episodes of 24 on during the
> > reporting period, so I only want to search on the name, and show the
> > earliest instance of each episode.

>
> SELECT p.*
> FROM programs p LEFT JOIN programs p2
> ON p.name = p2.name AND p.episode = p2.episode AND p.start > p2.start
> WHERE p2.start IS NULL;
>
> In other words, show programs for which there is no other showing of the
> same episode with an earlier start time.
>
> Regards,
> Bill K.

Ack, when you folks start joining tables to themselves, that's when my
eyes start glazing over! It almost works, but if I try to define a
reporting period I run into troubles. I have 2 weeks worth of data in
the database, and only want X number of days reported on. Episodes that
were aired before that period don't show up; filtering inside the
join's ON clause didn't seem to work for me. Any suggestions?

[miken32]

miken32 wrote:

>> Bill Karwin wrote:
>> SELECT p.*
>> FROM programs p LEFT JOIN programs p2
>> ON p.name = p2.name AND p.episode = p2.episode AND p.start > p2.start
>> WHERE p2.start IS NULL;
>>
>> In other words, show programs for which there is no other showing of the
>> same episode with an earlier start time.
>>
>> Regards,
>> Bill K.

>
> Ack, when you folks start joining tables to themselves, that's when my
> eyes start glazing over! It almost works, but if I try to define a
> reporting period I run into troubles. I have 2 weeks worth of data in
> the database, and only want X number of days reported on. Episodes that
> were aired before that period don't show up; filtering inside the
> join's ON clause didn't seem to work for me. Any suggestions?

Ah, never mind. I filtered the date on p2 (instead of p) inside the ON
clause and that's the business! Thanks very much.

[miken32]

Bill Karwin wrote:

> SELECT p.*
> FROM programs p LEFT JOIN programs p2
> ON p.name = p2.name AND p.episode = p2.episode AND p.start > p2.start
> WHERE p2.start IS NULL;
>
> In other words, show programs for which there is no other showing of the
> same episode with an earlier start time.
>
> Regards,
> Bill K.

Although, my original statement was in reality more like this:
SELECT start, channel, COUNT(episode) FROM programs WHERE name='24'
GROUP BY episode

If you could suggest a way to get a count in there (so I could spot
episodes with more than one airing and flag them) it would be wonderful.

[Bill Karwin]

miken32 wrote:

> If you could suggest a way to get a count in there (so I could spot
> episodes with more than one airing and flag them) it would be wonderful.

If I were doing this application, I would do the count in a separate
query.

In general, it's not efficient or appropriate to do too many things in a
single SQL statement. Sometimes it is possible to do so, but it's
likely to involve contortions that make your code difficult to maintain.

Regards,
Bill K.