Re: SQL problem. return record id in a Group by select query?

Try the following statement:

select LineNo, LineType, ID
from XYZ A
where LineType = (select max(LineType)
from XYZ B
where A.LineNo = B.LineNo);

Depending on the analyzed data you retrieve multiple rows for one LineNo; this happens, if there are multiple records with the maximum LineType for a specifc LineNo. If this will be a problem to you, please remail with a specification, which row should be returned in such a case.

Bye
Thomas

mdlcpgs_at_lhn.gns.cri.nz.nospam wrote:

> I am being beaten by what I am sure is relatively simple SQL problem.
>
> I have table structure of
>
> Lineno (character)
> LineType (numeric code)
> ID (unique numeric id)
>
> I want to group by Lineno and get the ID of the record with
> MAX(Linetype)
>
> SELECT lineno, Max(lineType) will fetch the record but I cant see how
> to
> return the ID.
>
> --------------
> -------------------------------------------------------------
> Phil Scadden, Institute of Geological and Nuclear Sciences
> Work: PO Box 30368, Lower Hutt Home: 55 Buick St, Petone,
> Lower Hutt
> New Zealand
> ph +64 (04) 569-9059, fax (04) 569 5016 ph (04) 568-7190,

--