Page 1 of 1

GROUP BY ordering / position

Posted: Wed Jul 02, 2008 11:26 am
by kendall
Hi guys,

I am selecting some records from a table whose results need to be group by an id. However, in grouping the results I need the grouping to select the last match entry rather than the first entry

[sql]SELECT DISTINCTROW *, CONCAT_WS(' ', parliament_members.parliamenttitle,parliament_contacts.FirstName,parliament_contacts.LastName,parliament_contacts.suffix) AS Representative    FROM      `parliament_members`      INNER JOIN `parliament_contacts` ON (`parliament_members`.`MemberId` = `parliament_contacts`.`id`)      LEFT JOIN `constituencies` ON (`parliament_members`.`ConstituencyId` = `constituencies`.`cid`)      WHERE `parliament_members`.`ParliamentId` = '5'         AND `parliament_members`.`ConstituencyId` IS NOT NULL         AND parliament_members.`Status` = 1        GROUP BY `parliament_members`.`MemberId` DESC      ORDER BY `parliament_members`.`FromDate` DESC[/sql]

basically i need to select the parliament_members however the the members will have duplicate member ids because it will contain updated information which will be indicated by the Fromdate. So i am trying to group the information showing only the latest how ever the group by apparantly groups to the first entry of the grouping.

and i.e. would be the following records in a table
64 42 {null} 33 5 11/8/2007 12:00:00 AM 4/22/2008 12:00:00 AM House of Representatives PNM {null} 1 Minister of Trade and Industry The Honourable Hon. {null}
...............................
600 42 {null} 33 5 4/23/2008 12:00:00 AM 12/16/2012 12:00:00 AM House of Representatives PNM {null} 1 Government Member {null} {null} {null}
however on the grouping the first entry "64" is used when grouping by the id "42"

How can i get it to return the latest entry which would be "600"

Re: GROUP BY ordering / position

Posted: Wed Jul 02, 2008 4:37 pm
by Kieran Huggins
You need to add a GROUP BY function to the mix, like MAX(Fromdate) - that way MySQL will return the row from each group with the highest Fromdate.

Te GROUP BY functions: http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

Re: GROUP BY ordering / position

Posted: Thu Jul 03, 2008 9:59 am
by kendall
I tried it....

while the max gave me the most recent date...the grouping didnt return the recent row.

i tried to use

[sql]GROUP BY ..., fromdate[/sql]

where fromdate is the max() but i got and sql error saying
Can't group on fromdate
which i don't understand as i have seen e.g. where the group by clause had made use of "hidden variables" as seen in the mysql documentation....

the results of the max yielded...
KRO01 Diego Martin West 4/23/2008 12:00:00 AM Minister of Trade and Industry PNM 33 11/8/2007 12:00:00 AM The Honourable Keith Rowley
note the from date '4/23/2008' however this doesnt corresponse to the row as shown in the e.g i had stated above