asked on

Grouping Query

A table in my database, just the columns that matter for this example.

Field Name Field Type
PropertyID Long
Owner Text
DefaultAddressYN YN
ActiveYN YN

Some date might be:

PropertyID DefaultAddr Active Owner
1111111 -1 -1 John Smith
1111111 0 -1 Bob Smith
2222222 0 0 John Jones
2222222 0 -1 Larry Jones
2222222 -1 0 Joe Glary
2222222 0 0 Andy Tows

-1 = Y in a YN field, 0 = No in a YN field

The datebase rules are supposed to be 'one and only one address on a property is the default'. Unfortunately I have no control over the integrity of the data I am working with and I've found the rule has not been enforced.

Every property does have at least one address, this has been enforced.

What I want to do:
Create a query returning one and only one address for a property.
If any address is a default I want that to be the one presented.
If none are selected as a default I want one of the 'Active' addresses selected.
If none are default or active it really doesn't matter which one is presented.

In the example above I would want my result query to contain:
1111111 John Smith -1 -1
2222222 Joe Glary -1 0

I've tried several query variations to get hwta I want but none return the correct result.

I did a query grouping PropertyID with Min on Default, Active and Name

I figured this would pull the any default to the top, if no default was found the active would be pulled to the top and the name would go along with it.

That's not how it worked out. The Min function just selected the lowest value in each column for each property. If there were any defaults for a property that column contained -1, if there were any actives that column contained a -1 and the name which sorted to the top alphabetically was always selected.

In the above example I'd end up with
1111111 -1 -1 Bob Smith
2222222 -1 -1 Andy Tows

Now I see why that didn't work but I'm stumped as to how I can get it to work the way I want.

I really don't see a way to do it in a query. Is it possible?

Brett Danney

A little confusing. Do you want to update (set) the default for all records that have no default or are you just trying to see any address for records with no default when you query it?

Hiran Desai

I thought you were looking forward for something like this.
in my case 1=-1

Create Table #Example
(
 PropertyID bigint
 ,Owner nvarchar(100)
 ,DefaultAddressYN bit
 ,ActiveYN  bit
)

Insert into #Example values(1111111,'John Smith',1,1)
Insert into #Example values(1111111,'Bob Smith',0,1)
Insert into #Example values(2222222,'John Jones',0,0)
Insert into #Example values(2222222,'Larry Jones',0,1)
Insert into #Example values(2222222,'Joe Glary',1,0)
Insert into #Example values(2222222,'Andy Tows',0,0)
Insert into #Example values(2222223,'Andy Tows',0,1)

Select *
from (
		Select *,dense_rank() over(PARTITION by PropertyID order by PropertyID,DefaultAddressYN desc,ActiveYN desc)	as t	
		from #Example		
		) as myData
		where myData.t=1	


drop table #Example

Open in new window

mlcktmguy

ASKER

SagiEDoc: Thanks for your response: Not sure what is confusing but I'll try to explain it again.

This is purely a select query, nothing is being updated.

In this address file there is at least one entry for each property but many properties have multiple entries.

In the result set of the query I want to select only one address for a property but I want it to be the 'best' address. If any of the address entries for a property are selected as the 'Default', that would be the 'best' address. If multiple addresses are selected as 'default' any of those would be the 'best'.

If none of the addresses have the 'DefaultYN' field checked then any address that is 'Active' will do.

If none of the addresses are identified a 'Default' or 'Active' it really doesn't matter which address record is selected.

ItWorked: Thanks for your response also but I don't understand the select statement you have used.

Select *,dense_rank() over(PARTITION by PropertyID order by PropertyID,DefaultAddressYN desc,ActiveYN desc) as t

I am not familiar with the function 'dense_rank()' or the meaning of 'over (PARTITION'.
Could you please explain further.
This is being done in an MSAccess query.

Hiran Desai

I thought you are SQL dev. But anyway please refer :
Access DB with dense_rank()

Dale Fye

Is there a Unique ID (pk) associated with each record in this table?

If so, I would start with a query that identifies the "first" record for each PropertyID:

SELECT PropertyID, Min(ID) as MinID
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1

Open in new window

I would then use that query to identify an "Active" record from each of the other PropertyID fields, the ones which do not have a record with DefaultAddr = -1. It would look something like:

SELECT PropertyID, Min(ID) as MinID
FROM yourTable
LEFT JOIN (
SELECT PropertyID, Min(ID) as MinID
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1
) as qryDefAdd
ON yourTable.PropertyID = qryDefAdd.PropertyID
where qyrDefAdd.MinID IS NULL
AND yourTable.Active = -1
GROUP BY PropertyID

Open in new window

If you union these together it will give you a set of PropertyID's and the MinID for each PropertyID where [Active] = -1. You can then join that back to your table on MinID to get whatever fields you want from each of those records. Something like:

Select yourTable.PropertyID, yourTable.DefaultAddr, yourTable.Active, yourTable.Owner
FROM (
SELECT PropertyID, Min(ID) as MinID
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1
UNION ALL
SELECT PropertyID, Min(ID) as MinID
FROM yourTable
LEFT JOIN (
SELECT PropertyID, Min(ID) as MinID
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1
) as qryDefAdd
ON yourTable.PropertyID = qryDefAdd.PropertyID
where qyrDefAdd.MinID IS NULL
AND yourTable.Active = -1
GROUP BY PropertyID
) as BestProperty
INNER JOIN yourTable
ON BestProperty.MinID = yourTable.MinID

Open in new window

You could do this using other fields (other than an ID) field, but you risk the chance of not having a unique value to use for each property. For example, you might be able to use:

Select yourTable.PropertyID, yourTable.DefaultAddr, yourTable.Active, yourTable.Owner
FROM (
SELECT PropertyID, Min(Owner) as MinOwner
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1
UNION ALL
SELECT PropertyID, Min(Owner) as MinOwner
FROM yourTable
LEFT JOIN (
SELECT PropertyID, Min(Owner) as MinOwner
FROM yourTable
WHERE DefaultAddr = -1 AND Active = -1
) as qryDefAdd
ON yourTable.PropertyID = qryDefAdd.PropertyID
where qyrDefAdd.MinOwner IS NULL
AND yourTable.Active = -1
GROUP BY PropertyID
) as BestProperty
INNER JOIN yourTable
ON BestProperty.MinOwner = yourTable.MinOwner

Open in new window

mlcktmguy

ASKER

fyed: I think I see where you're going but I get stopped right away when I try the first query because PropertyID is not part of an aggregate function. This is what I have:

SELECT tblProperty_Addresses.PropertyID, Min(tblProperty_Addresses.ID) AS MinID
FROM tblProperty_Addresses
WHERE (((tblProperty_Addresses.DefaultAddress_YN)=-1) AND ((tblProperty_Addresses.Active_YN)=-1));

Open in new window

Also there is a possibility that a property has only addresses on which neither active or default is selected.
It is also possible for an property to have more than one address with default selected.
The only thing for certain is that there will always be at least one address for a property.
I have no control over the integrity of this data.

Dale Fye

Sorry, forgot the Group By clause in the first query. That should look like:

SELECT tblProperty_Addresses.PropertyID, Min(tblProperty_Addresses.ID) AS MinID
FROM tblProperty_Addresses
WHERE tblProperty_Addresses.DefaultAddress_YN=-1
AND tblProperty_Addresses.Active_YN=-1
GROUP BY  tblProperty_Addresses.PropertyID  '<---- Add this line

Open in new window

You would also need to add that line in several places in the larger query:

Select tblProperty_Addresses.PropertyID
, tblProperty_Addresses.DefaultAddr
, tblProperty_Addresses.Active
, tblProperty_Addresses.Owner
FROM (
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addresses
WHERE DefaultAddr = -1 AND Active = -1
GROUP BY PropertyID
UNION ALL
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addressestbl
LEFT JOIN (
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addresses
WHERE DefaultAddr = -1 AND Active = -1
GROUP BY PropertyID
) as qryDefAdd
ON tblProperty_Addresses.PropertyID = qryDefAdd.PropertyID
where qyrDefAdd.MinID IS NULL
AND tblProperty_Addresses.Active = -1
GROUP BY tblProperty_Addresses.PropertyID
) as BestProperty
INNER JOIN tblProperty_Addresses
ON BestProperty.MinID = tblProperty_Addresses.MinID

Open in new window

awking00

If there were records such as -

33333333 0 -1 John Doe
33333333 0 -1 Jane Doe

Which record would you want and what's the criteria for that choice?

mlcktmguy

ASKER

awking00: In that situation it doesn't matter which record is selected, if a criterion is necessary I could use 'DateAdded' which is also stamped on every record. Meaning the assumption would be the record which has been added most recently would be 'better'.

Dale Fye

If more recent is better, then I would use MAX() instead of Min() and would use the DateAdded field instead of ID in my previous query.

mlcktmguy

ASKER

Good suggestion fyed but I'm still having issues getting the original to work. I'm getting a 'Syntax Error In Join Operation'. I cut and pasted exactly what you posted into my query. Here's what I have:

Select tblProperty_Addresses.PropertyID
, tblProperty_Addresses.DefaultAddr
, tblProperty_Addresses.Active
, tblProperty_Addresses.Owner
FROM (
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addresses
WHERE DefaultAddr = -1 AND Active = -1
GROUP BY PropertyID
UNION ALL
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addressestbl
LEFT JOIN (
SELECT PropertyID, Min(ID) as MinID
FROM tblProperty_Addresses
WHERE DefaultAddr = -1 AND Active = -1
GROUP BY PropertyID
) as qryDefAdd
ON tblProperty_Addresses.PropertyID = qryDefAdd.PropertyID
where qyrDefAdd.MinID IS NULL
AND tblProperty_Addresses.Active = -1
GROUP BY tblProperty_Addresses.PropertyID
) as BestProperty
INNER JOIN tblProperty_Addresses
ON BestProperty.MinID = tblProperty_Addresses.MinID

Open in new window

awking00

select propertyid, owner, defaultaddr, active from
(select propertyid, defaultaddr, active, owner, dateadded,
row_number() over (partition by propertyid order by defaultaddr, active, dateadded desc) rn
from yourtable)
where rn = 1;

mlcktmguy

ASKER

awking00: Thank you but this is an MS Access query, the functionality that you are using is not available in that environment.

Dale Fye

Line 12 reads :

FROM tblProperty_Addressestbl

but should read:

FROM tblProperty_Addresses

mlcktmguy

ASKER

Thanks, still getting errors. I got 'PropertyID could refer to more than one table listed in the from clause of your sql statement

I added 'tblAddresses' qualifiers to all the references to PropertyID but then got the same error on 'tblProperty_Addesses.Active'.

I wish I could debug it myself, I know these are just syntax errors but this query is way above my SQL expertise. I have never user Union ALL

Rather than combine things I usually do my queries in steps using the query designer. In this case, based on your example, I create two queries in MS Access:

'qryProperty_Addresses_Best_1_DefAndActive' with this SQL code:

SELECT tblProperty_Addresses.PropertyID, Min(tblProperty_Addresses.ID) AS MinAddrRecID
FROM tblProperty_Addresses
WHERE (((tblProperty_Addresses.DefaultAddress_YN)=True) AND ((tblProperty_Addresses.Active_YN)=True))
GROUP BY tblProperty_Addresses.PropertyID;

Open in new window

The result set of query 1 is a unique entry for all PropertyID's with Default and Active Selected.

and another query 'qryProperty_Addresses_Best_2_OnlyActive' with this SQL:

SELECT tblProperty_Addresses.[PropertyID], Min(tblProperty_Addresses.[ID]) AS MinAddrRecID
FROM tblProperty_Addresses LEFT JOIN qryProperty_Addresses_Best_1_DefAndActive ON tblProperty_Addresses.ID = qryProperty_Addresses_Best_1_DefAndActive.MinAddrRecID
WHERE (((tblProperty_Addresses.Active_YN)=True) And ((qryProperty_Addresses_Best_1_DefAndActive.MinAddrRecID) Is Null))
GROUP BY tblProperty_Addresses.[PropertyID];

Open in new window

The result set for query 2 is a a unique entry for all PropertyID's that weren't in query 1, with Active selected

My next query would have to 'Union ALL', combining the result sets from the first two queries. I just don't know how to code a Union All query to do that.

Then I would need another query to contain a unique entry for all PropertID's *Not in query 1 or query 2) that have neither Active or Default selected. I can code that one if I have the result set from my Union ALL of qry 1 and qry 2.

Then the final query would 'Union All' of the propertyID's is those three result sets to a single result set.

I could use the result set from the last query, joined with tblProperty_Addresses to pull the appropriate owner name.

Any help would be greatly appreciated. I know my approach is remedial but I hate putting code into an application that I don't understand because it stops working I don't know how to correct it.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

fyed: Thanks for all of your help. All of your answers helped get to the eventual solution. I finally pulled all the bits together and got it to work and it's even done in a way that is understandable to my remedial SQL skill.

Dale Fye

glad I could help and that you finally got it working the way you needed.