Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: "Bob Barrows" <reb01501@NOSPAMyahoo.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: I Need help with a query...
Date: Tue, 5 Apr 2011 07:17:59 -0400
Organization: A noiseless patient Spider
Lines: 53
Message-ID: <inetog$514$1@dont-email.me>
References: <ab761644-26ae-41c4-a599-acb60a80ae69@n12g2000yqc.googlegroups.com>
Injection-Date: Tue, 5 Apr 2011 11:17:36 +0000 (UTC)
Injection-Info: mx02.eternal-september.org; posting-host="inlgCTpOMxujS+VHkVo6dA"; logging-data="5156"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1+QicOiMuXPwANjEELr77J1WORFr2P/IZ0="
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
X-RFC2646: Format=Flowed; Original
X-Antivirus-Status: Clean
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-Antivirus: avast! (VPS 110404-1, 04/04/2011), Outbound message
Cancel-Lock: sha1:U3gTyQgKPv+Xffsh3oL11JD3G2o=
X-Priority: 3
X-MSMail-Priority: Normal
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:118

Alfaking wrote:
> hello.
> I need help with a query please...
> I've a table like this
>
> Id | Contr | Type
>
> the content of this table is like
>
> Id  | Contract | Type
> 1   | 1250  | W
> 2   | 1250  | T
> 3   | 5555  | W
> 4   | 6666  | T
> 5   | 7777  | W
> 6   | 6666  | W
> 7   | 6666  | W
> 8   | 6666  | T
> 9   | 7777  | W
> 10  | 1250 | W
> 11  | 1250 | T
> 12  | 1250 | W
> 13  | 1250 | T
> 14  | 1250 | T
>
> the key is the ID
>
> I need to extract all the "Contr" where type is not "T"
> so i should have as result
> 3  | 5555 | W
> 5  | 7777 | W
>
> whenever i found a type "T", I have to exclude that number of "contr".
>
> I'm able to exclude all the records where type is not "T", but if I
> found type ="T", that contr goes into results because of the ID
> field...
>
> any idea of how I can do this ?
>
> thanks in advance
>
> p.s.sorry for my english, I'm Italian. I really hope you can
> understand me...

Are 'W' and "T" the only allowable types? If so:

SELECT min(id),Contr,max(Type)
FROM table
GROUP BY Contr
HAVING max(Type)<>'T'