This returns mysql> Select Field1, Field2, MAX(IDField) From amain Group by Field1; +--------+--------+--------------+ | Field1 | Field2 | MAX(IDField) | +--------+--------+--------------+ | A | 1 | 4 | | B | 7 | 8 | | C | 1 | 12 | +--------+--------+--------------+
The correct answer is > select field1, last(field2) from table1 group by field1 > Feild1 Field2 > A 3 > B 8 > C 1 I may have a solution, check out the posts on the newslist :o) -----Original Message----- From: Noel Clarkson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 9 July 2002 05:37 To: 'Miles Roper'; '[EMAIL PROTECTED]' Subject: RE: UDF, Can anyone please help? So if below is actually what your data looks like, what about: Select Field1, Field2, MAX(IDField) From MyTable Group by Field1 That might get the correct answer for you, but it does however have an extra field in the result (which may or may not be a problem). cheers, noel On Tuesday, July 09, 2002 7:03 AM, Miles Roper [SMTP:[EMAIL PROTECTED]] wrote: > Hi Frank, > > Good idea, but I actually need the last value, ie > > IDField Field1 Field2 > 1 A 1 > 2 A 4 > 3 A 2 > 4 A 3 > 5 B 7 > 6 B 10 > 7 B 3 > 8 B 8 > 9 C 1 > 10 C 2 > 11 C 4 > 12 C 1 > > The answer would be > > select field1, last(field2) from table1 group by field1 > > Feild1 Field2 > A 3 > B 8 > C 1 > > Select Field1, Max( Field2 ) From MyTable Group by Field1 > > Would produce a wrong answer > > Feild1 Field2 > A 4 > B 10 > C 4 > > I'm keeping an eye on the mailing list via the net. :o) I get to many > emails as it is :o) > > Hope this makes sense :o) Thanks for you help. > > Cheers > > Miles > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
