Retrieving rows with minimum values within a column

3 Apr 2006 1:20 PM

MACason

I am attempting to retrieve only the rows in a table that have the minimum
value of the adr_numb column for each occurrence of the adr_code column. When
I run the below query, it still returns all the rows within the table. Any
suggestions on how to restructure this query to obtain the correct results
would be appreciated.

Select *
From dbo.addr_tbl a
Where exists (Select Min(adr_numb)
                 From dbo.addr_tbl b
                Where a.adr_code = b.adr_code)

Current rows:

adr_code              adr_numb

4M3IWNFP51    1
4M3IWNFP51    2
9UAZZRD5U1    1
C0VCLF5001    1
C0VCLF5001    2
DKZKR1ZFH1    2
F9D599KH01    1
F9D599KH01    2
F9D599KH01    3
FAPCM71YH1    2
FAPCM71YH1    3

Desired Results:

adr_code              adr_numb

4M3IWNFP51    1
9UAZZRD5U1    1
C0VCLF5001    1
DKZKR1ZFH1    2
F9D599KH01    1
FAPCM71YH1    2

3 Apr 2006 1:35 PM

Madhivanan

Select * from table where adr_numb in
(select min(adr_numb) from table group by adr_code)

Madhivanan

3 Apr 2006 1:44 PM

Omnibuzz

Select adr_code,Min(adr_numb)
From dbo.addr_tbl
group by adr_code

3 Apr 2006 2:01 PM

Omnibuzz

and if the table has more than the columns you had shown, it should go like
this

Select * from table a where adr_numb =
(select min(adr_numb) from table b where a.adr_code = b.adr_code)

P.S: Madhivanan, Can you check your query. I feel it might give an erronous
output.

3 Apr 2006 2:20 PM

Alejandro Mesa

Try,

select *
from dbo.addr_tbl as a
where not exists (
select *
from dbo.addr_tbl as b
where b.adr_code = a.adr_code and b.adr_numb < a.adr_numb
)
go

AMB

Show quoteHide quote

"MACason" wrote:

> I am attempting to retrieve only the rows in a table that have the minimum
> value of the adr_numb column for each occurrence of the adr_code column. When
> I run the below query, it still returns all the rows within the table. Any
> suggestions on how to restructure this query to obtain the correct results
> would be appreciated.
>
>
>
> Select *
>   From dbo.addr_tbl a
> Where exists (Select Min(adr_numb)
>                  From dbo.addr_tbl b
>                 Where a.adr_code = b.adr_code)
>
>
> Current rows:
>
> adr_code              adr_numb
>
> 4M3IWNFP51    1
> 4M3IWNFP51    2
> 9UAZZRD5U1    1
> C0VCLF5001    1
> C0VCLF5001    2
> DKZKR1ZFH1    2
> F9D599KH01    1
> F9D599KH01    2
> F9D599KH01    3
> FAPCM71YH1    2
> FAPCM71YH1    3
>
>
>
>
> Desired Results:
>
> adr_code              adr_numb
>
> 4M3IWNFP51    1
> 9UAZZRD5U1    1
> C0VCLF5001    1
> DKZKR1ZFH1    2
> F9D599KH01    1
> FAPCM71YH1    2
>

3 Apr 2006 3:03 PM

Jim Underwood

You almost had it, but you need to change the exists to equals.
When you use exists it is returning all rows where adr_code exists in at
least one other row in the table with a minimum adr_numb, which is true for
all rows expect those where adr_numb is null.

If you use equals, along with the "join" that you already have in your
subquery, you will only get back rows where the adr_numb is the minimum
value for each adr_code.

Select *
From dbo.addr_tbl a
Where adr_numb = (Select Min(b.adr_numb)
From dbo.addr_tbl b
Where a.adr_code = b.adr_code)

Show quoteHide quote

"MACason" <MACa***@discussions.microsoft.com> wrote in message
news:0B11BBE1-7345-45FD-9B82-7D2670596C69@microsoft.com...
> I am attempting to retrieve only the rows in a table that have the minimum
> value of the adr_numb column for each occurrence of the adr_code column.
When
> I run the below query, it still returns all the rows within the table. Any
> suggestions on how to restructure this query to obtain the correct results
> would be appreciated.
>
>
>
> Select *
>   From dbo.addr_tbl a
> Where exists (Select Min(adr_numb)
>                  From dbo.addr_tbl b
>                 Where a.adr_code = b.adr_code)
>
>
> Current rows:
>
> adr_code              adr_numb
>
> 4M3IWNFP51 1
> 4M3IWNFP51 2
> 9UAZZRD5U1 1
> C0VCLF5001 1
> C0VCLF5001 2
> DKZKR1ZFH1 2
> F9D599KH01 1
> F9D599KH01 2
> F9D599KH01 3
> FAPCM71YH1 2
> FAPCM71YH1 3
>
>
>
>
> Desired Results:
>
> adr_code              adr_numb
>
> 4M3IWNFP51 1
> 9UAZZRD5U1 1
> C0VCLF5001 1
> DKZKR1ZFH1 2
> F9D599KH01 1
> FAPCM71YH1 2
>

3 Apr 2006 5:04 PM

MACason

Thanks, Jim. Worked great. Should have posted sooner as I have been trying to
resolve this for the last couple days.

Show quoteHide quote

"Jim Underwood" wrote:

> You almost had it, but you need to change the exists to equals.
> When you use exists it is returning all rows where adr_code exists in at
> least one other row in the table with a minimum adr_numb, which is true for
> all rows expect those where adr_numb is null.
>
> If you use equals, along with the "join" that you already have in your
> subquery, you will only get back rows where the adr_numb is the minimum
> value for each adr_code.
>
> Select *
>   From dbo.addr_tbl a
> Where adr_numb = (Select Min(b.adr_numb)
>                  From dbo.addr_tbl b
>                 Where a.adr_code = b.adr_code)
>
> "MACason" <MACa***@discussions.microsoft.com> wrote in message
> news:0B11BBE1-7345-45FD-9B82-7D2670596C69@microsoft.com...
> > I am attempting to retrieve only the rows in a table that have the minimum
> > value of the adr_numb column for each occurrence of the adr_code column.
> When
> > I run the below query, it still returns all the rows within the table. Any
> > suggestions on how to restructure this query to obtain the correct results
> > would be appreciated.
> >
> >
> >
> > Select *
> >   From dbo.addr_tbl a
> > Where exists (Select Min(adr_numb)
> >                  From dbo.addr_tbl b
> >                 Where a.adr_code = b.adr_code)
> >
> >
> > Current rows:
> >
> > adr_code              adr_numb
> >
> > 4M3IWNFP51 1
> > 4M3IWNFP51 2
> > 9UAZZRD5U1 1
> > C0VCLF5001 1
> > C0VCLF5001 2
> > DKZKR1ZFH1 2
> > F9D599KH01 1
> > F9D599KH01 2
> > F9D599KH01 3
> > FAPCM71YH1 2
> > FAPCM71YH1 3
> >
> >
> >
> >
> > Desired Results:
> >
> > adr_code              adr_numb
> >
> > 4M3IWNFP51 1
> > 9UAZZRD5U1 1
> > C0VCLF5001 1
> > DKZKR1ZFH1 2
> > F9D599KH01 1
> > FAPCM71YH1 2
> >
>
>
>

4 Apr 2006 1:23 PM

Madhivanan

Thanks Omnibuzz

Madhivanan