[DUG] sql - GROUP

[Bevan Edwards]

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/20131110/5058c6bc/attachment-0002.html