Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL problem. return record id in a Group by select query?
SELECT LineNo, Max(LineType), ID
FROM Table
GROUP BY LineNo;
should do the trick. Any columns in the query result that appear after the GROUPing columns will only return one value.
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,
Received on Wed Apr 01 1998 - 00:00:00 CST