GROUP BY ordering / position

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

GROUP BY ordering / position

Post 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"
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: GROUP BY ordering / position

Post 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
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Re: GROUP BY ordering / position

Post 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
Post Reply