Help with Select Minimum Value

microsoft.public.sqlserver.programming

23 Mar 2006 1:13 PM

Stephen K. Miyasato

I need help in doing a select statment with a minimum
here is the statement

CREATE TABLE [Test] (
[rIndex] [int] IDENTITY (1, 1) NOT NULL ,
[Defaul_] [int] NULL ,
[FilterType] [int] NULL ,
[ProtScr] [int] NULL ,
[ProtRank] [int] NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ageStart] [int] NULL ,
[ageStop] [int] NULL ,
[Sex] [int] NULL ,
CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
(
[rIndex]
) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Description,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'FOBT',50, 90,1)

insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Description,
AgeStart, AgeStop,Sex)
values
(1,1,1,3,'Sigmoidoscopy',50, 90,1)

insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Description,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'Colonoscopy',65, 90,1)

I have a subquery to try and get the minimum ProtScr and ProtRank
When I do the subquery
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)

I get 2 values
ProtScr ProtRank
1 3
1 5

if possible I want the subquery to only pick the lower value only. The main
select statement that I tried to do is located below to select the values
based on the subquery.

Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where ProtRank EXISTS in
(Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1))

Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))

Thanks

Stephen K. Miyasato

23 Mar 2006 1:21 PM

Uri Dimant

Hi

SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr

Show quote

"Stephen K. Miyasato" <miya***@flex.com> wrote in message
news:OkzZlunTGHA.5496@TK2MSFTNGP11.phx.gbl...
>I need help in doing a select statment with a minimum
> here is the statement
>
> CREATE TABLE [Test] (
> [rIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [Defaul_] [int] NULL ,
> [FilterType] [int] NULL ,
> [ProtScr] [int] NULL ,
> [ProtRank] [int] NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ageStart] [int] NULL ,
> [ageStop] [int] NULL ,
> [Sex] [int] NULL ,
> CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
> (
> [rIndex]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Description,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'FOBT',50, 90,1)
>
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Description,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,3,'Sigmoidoscopy',50, 90,1)
>
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Description,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'Colonoscopy',65, 90,1)
>
> I have a subquery to try and get the minimum ProtScr and ProtRank
> When I do the subquery
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
>
> I get 2 values
> ProtScr ProtRank
> 1 3
> 1 5
>
> if possible I want the subquery to only pick the lower value only. The
> main select statement that I tried to do is located below to select the
> values based on the subquery.
>
> Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
> ageStart,AgeStop,sex from Test
> where ProtRank EXISTS in
> (Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
>
> Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
>
> Thanks
>
> Stephen K. Miyasato
>
>

23 Mar 2006 5:45 PM

Stephen K. Miyasato

Thanks that helped

I get
protScr ProtRank
1 3

The subquery works but I could not get the main query to work

Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where protScr, ProtRank EXISTS in -- need some help here

( -- subquery begins
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
) -- subquery ends

Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))

Thanks

Stephen K. Miyasato

Show quote

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:e8bbKynTGHA.776@TK2MSFTNGP09.phx.gbl...
> Hi
>
> SELECT ProtScr,MIN(ProtRank) AS ProtRank
> FROM
> (
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> ) AS Der
> GROUP BY ProtScr
>
>
>
> "Stephen K. Miyasato" <miya***@flex.com> wrote in message
> news:OkzZlunTGHA.5496@TK2MSFTNGP11.phx.gbl...
>>I need help in doing a select statment with a minimum
>> here is the statement
>>
>> CREATE TABLE [Test] (
>> [rIndex] [int] IDENTITY (1, 1) NOT NULL ,
>> [Defaul_] [int] NULL ,
>> [FilterType] [int] NULL ,
>> [ProtScr] [int] NULL ,
>> [ProtRank] [int] NULL ,
>> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> [ageStart] [int] NULL ,
>> [ageStop] [int] NULL ,
>> [Sex] [int] NULL ,
>> CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
>> (
>> [rIndex]
>> ) ON [PRIMARY]
>> ) ON [PRIMARY]
>> GO
>>
>> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Description,
>> AgeStart, AgeStop,Sex)
>> values
>> (1,1,1,5,'FOBT',50, 90,1)
>>
>> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Description,
>> AgeStart, AgeStop,Sex)
>> values
>> (1,1,1,3,'Sigmoidoscopy',50, 90,1)
>>
>> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Description,
>> AgeStart, AgeStop,Sex)
>> values
>> (1,1,1,5,'Colonoscopy',65, 90,1)
>>
>> I have a subquery to try and get the minimum ProtScr and ProtRank
>> When I do the subquery
>> Select ProtScr, MIN(ProtRank) AS ProtRank from test
>> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
>> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
>> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
>>
>> I get 2 values
>> ProtScr ProtRank
>> 1 3
>> 1 5
>>
>> if possible I want the subquery to only pick the lower value only. The
>> main select statement that I tried to do is located below to select the
>> values based on the subquery.
>>
>> Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
>> ageStart,AgeStop,sex from Test
>> where ProtRank EXISTS in
>> (Select ProtScr, MIN(ProtRank) AS ProtRank from test
>> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
>> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
>> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
>>
>> Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
>> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
>>
>> Thanks
>>
>> Stephen K. Miyasato
>>
>>
>
>