Grouping By returns first record

Ask about general coding issues or problems here.

Moderators: egami, macek, gesf

Post Reply
Indro
New php-forum User
New php-forum User
Posts: 7
Joined: Mon May 07, 2018 5:06 am

Mon May 21, 2018 9:25 pm

Hello,

I have several events, and each event has its edition (edition 1, 2, 3, 4, etc.).

For example, I have

EventA, edition 1, ID 1, NameID 1
EventA, edition 2, ID 2, NameID 1
EventA, edition 3, ID 3, NameID 1
EventB, edition 1, ID 4, NameID 2
EventB, edition 2, ID 5, NameID 2
etc.

I am trying to list, for each event, the one with most editions.

Currently my query is:

Code: Select all

$sql="select * from Event WHERE Event_Edition > 0 AND Event_Name !='' GROUP BY NameID ORDER BY Event_Edition DESC";
however this returns

EventA, edition 1, ID 1, NameID 1
EventB, edition 1, ID 4, NameID 2

and not the correct

EventA, edition 3, ID 3, NameID 1
EventB, edition 2, ID 5, NameID 2

In other words, groups the event by their NameID correctly but then it pick the one with the lower ID instead of picking the one with the highest Edition

chorn
php-forum Fan User
php-forum Fan User
Posts: 551
Joined: Fri Apr 01, 2016 2:18 am

Tue May 22, 2018 3:20 am

Every column that is not in the GROUP BY clause must be aggregated, with e.g. SUM, AVG, MIN, MAX,, or whatever you think might be suitable. Otherwise you get random results. More sophisticated databases would give you an error, MySQL just gives you fucked up results.

Indro
New php-forum User
New php-forum User
Posts: 7
Joined: Mon May 07, 2018 5:06 am

Tue May 22, 2018 12:50 pm

Thank you for your reply.

If I run this query with MAX

$sql="select * from Event WHERE max(Event_Edition) > 0 AND Event_Name !='' GROUP BY NameID ORDER BY Event_Edition DESC";

I get an error - Incorrect database query: Invalid use of group function

chorn
php-forum Fan User
php-forum Fan User
Posts: 551
Joined: Fri Apr 01, 2016 2:18 am

Tue May 22, 2018 11:17 pm

EVERY (!) column that is not in the group by clause must be aggrgated, you can't write "select *" here.

Indro
New php-forum User
New php-forum User
Posts: 7
Joined: Mon May 07, 2018 5:06 am

Wed May 23, 2018 7:43 pm

Thanks again. I will do some research, as I am not familiar with query that do not use "select *".

seandisanti
php-forum Fan User
php-forum Fan User
Posts: 974
Joined: Mon Oct 01, 2012 12:32 pm

Sat May 26, 2018 10:12 pm

The asterisk in SELECT * is a wildcard to designate that you wish to select all fields from the table. Because you are using an aggregate function (count / sum / etc) you must have a GROUP BY clause which should include each field that is being selected (in your select clause) but is not an aggregate. You can still do a SELECT *, but you will have to explicitly type out each field name that qualifies into your GROUP BY.

Post Reply