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

Hi,

try:

SELECT t1.lineID,t1.lineno, t1.lineType

	FROM
		(SELECT 
			lineno, Max(lineType) lineType
			FROM
				your_table
		) t2,
		your_table t1
	WHERE
		t1.lineno=t2.lineno
		AND
		t2.linetype=t1.linetype

On Thu, 2 Apr 98 13:01:48 +1200, 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,

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de