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

From: <mdlcpgs_at_lhn.gns.cri.nz.nospam>
Date: 1998/04/03
Message-ID: <6g116s$ipp$1@wnnews1.netlink.net.nz>#1/1

>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.

Would be nice but SQL doesnt allow it. If I try it gives me : ORA 0979 message.
The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.

Original message:
>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 Fri Apr 03 1998 - 00:00:00 CST