GROUP BY ordering / position
Posted: Wed Jul 02, 2008 11:26 am
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
How can i get it to return the latest entry which would be "600"
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
however on the grouping the first entry "64" is used when grouping by the id "42"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}
How can i get it to return the latest entry which would be "600"