Retrieving rows with minimum values within a column

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. Whe
n
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 2Select * from table where adr_numb in
(select min(adr_numb) from table group by adr_code)
Madhivanan|||Select adr_code,Min(adr_numb)
From dbo.addr_tbl
group by adr_code|||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.|||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
"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. W
hen
> 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
>|||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" <MACason@.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
>|||Thanks, Jim. Worked great. Should have posted sooner as I have been trying t
o
resolve this for the last couple days.
"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 fo
r
> 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" <MACason@.discussions.microsoft.com> wrote in message
> news:0B11BBE1-7345-45FD-9B82-7D2670596C69@.microsoft.com...
> When
>
>|||Thanks Omnibuzz
Madhivanan