Help with Select Minimum Value

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,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'FOBT',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,3,'Sigmoidoscopy',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
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. MiyasatoHi
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" <miyasat@.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,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'FOBT',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,3,'Sigmoidoscopy',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> 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
>|||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
"Uri Dimant" <urid@.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" <miyasat@.flex.com> wrote in message
> news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>