Thanks guys
I found the problem; I should have assigned max(Number) AS Number, as further up in my code it could not find the field "max(Number)", but now with "AS Number" it is all working. Cheers John From: [email protected] [mailto:[email protected]] On Behalf Of Bevan Edwards Sent: Sunday, 10 November 2013 8:16 p.m. To: NZ Borland Developers Group - Delphi List; NZ Borland Developers Group - Delphi List Subject: Re: [DUG] sql - GROUP Hi John, How about this, off the top of my head: select itemid, name, max(number) from table group by itemid, name order by name, itemid Regards, Bevan On 10/11/2013 20:09, John C wrote: Hi all Can anybody help me with the following query? I have a table like: ItemID Name Number 234 Washer 100 234 Washer 120 234 Washer 105 500 Nut 520 500 Nut 502 500 Nut 501 What I would like to see is a for each ItemID the highest Number sorted by Name. My sql is like: SELECT * FROM Table GROUP BY ItemID ORDER BY Name, Number What returns is: ItemID Name Number 234 Washer 105 500 Nut 501 It seems to return the last record for each item. How can I get the highest number for each Item? Thanks a lot for any help. John C _______________________________________________ NZ Borland Developers Group - Delphi mailing list Post: [email protected] Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [email protected] with Subject: unsubscribe
_______________________________________________ NZ Borland Developers Group - Delphi mailing list Post: [email protected] Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [email protected] with Subject: unsubscribe
