[DUG] sql - GROUP

[John C]

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: delphi-bounces at listserver.123.net.nz
[mailto:delphi-bounces at listserver.123.net.nz] 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: delphi at listserver.123.net.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-request at listserver.123.net.nz with
Subject: unsubscribe

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20131111/0175e3a8/attachment.html