changing the way GROUP BY works?

pck76

Hi all,

I have a database of events where sometimes different users could insert the same event (eg, a concert can be inserted by the venue owner, the band, a promoter etc.).

These users have a different 'priority', so a record inserted by a venue owner is considered more reliable than one inserted by a band etc).

eg:
[INDENT]table events_table:
eventid | band_playing | date | priority | other fields here...
100 | Stormbeats | 2009-10-03 | 1 | ...
109 | Stormbeats | 2009-10-03 | 0 | ...
104 | Stormbeats | 2009-10-03 | 2 | ...
[/INDENT]
I have to show only one row for that concert, and that row should be the one with highest priority.

With "SELECT * from events_table WHERE date='2009-10-03' GROUP BY band" the result is only one row, but this would be random.

I've tried "...GROUP BY band ORDER BY priority DESC" but it doesn't work.

How can I get the data from the record with highest priority?

thanks,
Patrick

Lars_Berg

select <columnsofInterest>
  from events e
 where priority =
    (select max(priority)
         from events
      where events.band_playing = e.band_playing
          and events.date = e.date)
   and date = '2009-10-03'

pck76

Thanks Lars

but can't make the code work.

what is that "e" in the second row of your post? the error seems to be there

Bjom

that's an alias. shortform for

FROM events AS e

Lars_Berg

What errror?

pck76

sorry that was my mistake, the error was probably a php error in my code.

Now i'm testing your suggestions directly with mysql (using phpmyadmin) and it works better, BUT there's a problem:

If there are 2 records with the highest priority, both are returned.

Is there a way to limit that always to one?

this is the sql I'm using, slightly modified:

SELECT * FROM eventlist AS e WHERE priority =
     (SELECT max(priority) FROM eventlist WHERE 
      eventlist.band = e.band AND eventlist.eventdate = e.eventdate)
AND eventdate = '2009-10-03' ORDER BY priority DESC, band DESC

Last, I'm not sure I understand how this query works - I imagined the secon query (SELECT MAX(...) would be processed BEFORE the other one, but can't understand how the second could refer to "e" since that should be the result of the first query....?😕 (sorry don't know if I made myself clear! 🙂

Weedpacket

pck76 wrote:
Is there a way to limit that always to one?

put LIMIT 1 on the end.

refer to "e" since that should be the result of the first query

, no; "e", as Bjom has already noted above, is just an abbreviation for "eventlist". And it's not the "first" query so much as the "outer" query: it contains the "SELECT max(priority)..." as a part of itself.