Group by and priority 1

[edrossi]

To all:

Man, I can not figure this out. This is what I am trying to do. Let's say I have a data set of:

Address Zip ID Decile
123 Main St 76132 1 7
123 Main St 76132 2 6
123 Main St 76132 3 1

I am trying to get a result that provides me with the id of the record with the minimum DECILE, but grouped by ADDRESS and ZIP giving me only on unique record per ADDDRESS and ZIP combined, but I need the right record. So the record I am looking for is:

123 Main St 76132 3 1

Here is what I have (works for min(ID)), but can not get the prioity on DECILE to work:

SELECT ID
FROM TABLE
WHERE ID IN (SELECT MIN(A.ID)
FROM TABLE A,
TABLE B
WHERE A.ID = B.ID
HAVING COUNT(A.ID) > 1
GROUP BY PRIMARY_ADDRESS_LINE_1, PRIMARY_ZIP_CD_5)

Any help would be great.

Thanks-

E

 

[twifosp]

You're making it too complicated.

Try this:

select address, zip, id, decile
from table t
where id = (
select min(decile)
from @temp
where address = t.address
and zip = t.zip)

 

[Golom]

Almost

select    address, zip, id, decile
from    table t
where    [b][COLOR=red]decile[/color][/b] = (
    select     min(decile) 
    from    @temp
    where    address = t.address
    and    zip = t.zip)

 

[twifosp]

Oops, thanks for catching the column mismatch.

 

[edrossi]

Not meaning to complicate things. In fact I should have included two data sets. So, what if the data set looked more like:

Address Zip ID Decile
123 Main St 76132 1 7
123 Main St 76132 2 6
123 Main St 76132 3 1
321 Davis St 76132 1 1
321 Davis St 76132 2 6
321 Davis St 76132 3 3

So the records I am looking for is:

123 Main St 76132 3 1
321 Davis St 76132 1 1

Thanks agian and sorry for the confusion.

E-

 

[twifosp]

The query was written with that in mind.

select    address, zip, id, decile
from    table t
where    decile = (
    select     min(decile) 
    from    @temp
    where    address = t.address
    and    zip = t.zip)

Try it and let us know the results.

 

[edrossi]

I am running it now. Will let you know...... Thanks E