Professional Web Applications Themes

Grouping and ordering - MySQL

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 ...

  1. #1

    Default Grouping and ordering

    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.

    miken32 Guest

  2. #2

    Default Re: Grouping and ordering

    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]
    Brian Wakem Guest

  3. #3

    Default Re: Grouping and ordering


    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.

    miken32 Guest

  4. #4

    Default Re: Grouping and ordering

    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.
    Bill Karwin Guest

  5. #5

    Default Re: Grouping and ordering


    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 Guest

  6. #6

    Default Re: Grouping and ordering


    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 Guest

  7. #7

    Default Re: Grouping and ordering

    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.

    miken32 Guest

  8. #8

    Default Re: Grouping and ordering

    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.
    Bill Karwin Guest

Similar Threads

  1. Help Ordering Records
    By DogBot in forum Coldfusion Database Access
    Replies: 4
    Last Post: June 9th, 02:07 PM
  2. table ordering
    By patbegg in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 25th, 12:17 AM
  3. Ordering by date ???
    By TomT in forum ASP Database
    Replies: 21
    Last Post: August 17th, 08:11 PM
  4. Ordering of recordsets
    By Ken Schaefer in forum ASP Database
    Replies: 2
    Last Post: August 20th, 12:45 PM
  5. [PHP] File ordering
    By Lowell Allen in forum PHP Development
    Replies: 1
    Last Post: August 12th, 02:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139