Choosing an id using differnet group by and another field as 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 right:

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

 

[mkrausnick]

So if I understand, you want all the address/zip combinations for records with the minimum decile?

How about:

select distinct address,zip from table a where a.decile in (select min(decile) as decile from table)

Mike Krausnick
Dublin, California

 

[craigsboyd]

mkrausnick

You're code will only ever return one record. You had the right thought, though you failed to filter your nested query by table a.


edrossi

Try the following code in VFP, I believe you'll find it solves your problem. Sometimes SQL problems like this can appeared decidedly more complex than are.

CREATE CURSOR crstemp (address c(50), zip c(5), ID i, decile i)

*!* let's mix them up good so we know the results aren't based on physical record order
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("456 main st", "76456", 6, 2)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("789 main st", "76789", 9, 7)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("789 main st", "76789", 8, 3)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("123 main st", "76132", 1, 7)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("789 main st", "76789", 7, 6)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("123 main st", "76132", 2, 6)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("456 main st", "76456", 5, 7)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("456 main st", "76456", 4, 6)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("123 main st", "76132", 3, 1)

*!* we want id 3, 6, 8 and decile column is shown just for info purposes
SELECT a.ID, a.decile ;
FROM crstemp a ;
WHERE a.decile IN (SELECT MIN(decile) ;
					FROM crstemp b ;
					WHERE b.address = a.address AND b.zip = a.zip)

http://www.sweetpotatosoftware.com

 

[edrossi]

Thanks mkrausnick.....

But what if I mix it up even further and show two of the same addresses falling into the same decile. If I create this data set:

*!* let's mix them up good so we know the results aren't based on physical record order
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("456 main st", "76456", 6, 2)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("789 main st", "76789", 9, 3)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("789 main st", "76789", 8, 3)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("123 main st", "76132", 1, 7)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("789 main st", "76789", 7, 6)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("123 main st", "76132", 2, 6)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("456 main st", "76456", 5, 7)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("456 main st", "76456", 4, 6)
INSERT INTO crstemp (address, zip, ID, decile) VALUES ("123 main st", "76132", 3, 1)

..... I will end up with a duplicate address for 789 main st. What you have done is awesome, but, is there any way to overcome this if a decile falls in the same address range.

Thanks,

E

 

[edrossi]

Oh sorry, thanks craigsboyd too. This was great.

 

[mkrausnick]

I guess I misundestood your need. I thought you needed the addresses that contained the minimum decile number.

Now I'm thinking that what you want is the ID of the record with the lowest decile number for EACH address. Is that right?

If so, then, using the data from your most recent post, the statement:

SELECT address,zip,MIN(decile) FROM crstemp GROUP BY address,zip

would yield:

Address         Zip        Decile
---------------------------------
123 main st    76132          1
456 main st    76456          2
789 main st    76789          3

That's great, but you need the ID of the records, so it gets more complicated. The statements

SELECT address,zip,MIN(decile) as decile FROM crstemp GROUP BY address,zip INTO CURSOR c_lowest_deciles
SELECT DISTINCT b.id,a.decile,a.address,a.zip FROM c_lowest_deciles a,crstemp b WHERE a.address=b.address AND a.zip=b.zip AND a.decile=b.decile

which are functionally equivelent to Craig's SQL, yield the result:

ID   Decile    Address         Zip        
-------------------------------------------
3      1       123 main st    76132
6      2       456 main st    76456
8      3       789 main st    76789
9      3       789 main st    76789

But as you point out, "789 main st" is duplicated.

That's because there are two records with the same address and lowest decile (IDs 8 and 9 both have "789 main st" and "76789" and decile 3). Fundamentally, it's a logic flaw - the existing criteria (address,zip,lowest decile) do not yield a unique result, so you need to define another criteria to uniquely identify which of the two IDs you want in this case.




Mike Krausnick
Dublin, California

 

[mkrausnick]

You might want to ponder, given your specific data and application, whether it would ever happen that two records would have the same address,zip,and lowest decile. If not, then the SQL Craig or I suggested would work. However, if such duplicate records ARE possible, then as I said, you either need to supply another criteria, or re-design the database so duplicates aren't possible.

Mike Krausnick
Dublin, California