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

From: <dean_at_mindless.com>
Date: 1998/04/02
Message-ID: <6g01th$f6v$1@nnrp1.dejanews.com>#1/1

Hi,

I think you can just use :-

select lineno,linetype,id
from yourtable A
where not exists
(select 'X'
 from yourtable B
 where A.lineno = B.lineno
and A.linetype < B.linetype )

Hope this helps.

Dean.

In article
<6fuo22$mg2$1_at_wnnews1.netlink.net.nz>,
  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,
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Apr 02 1998 - 00:00:00 CST