Tie Breaker Query

[DerPflug]

I have a non-normalized table that I'm trying to get certain results from and its posing a bit of a problem. The table is organized as follows (all fields are text):

ID FirstName LastName Address1 City State Zip DateConfirmed

12345 Bugs Bunny 123 Hello Street Topeka KS 22222 12/1/1985
12345 Bugs Bunny KS 22222 12/1/1985
12345 Bugs Bunny 555 What Lane Topeka KS 22222 1/1/1980
54321 Elmer Fudd 234 Looney Street Detriot MI 88888 11/5/2000
54321 Elmer Fudd 678 Penny Lane Detroit MI 88888 7/5/2010

What I need to do is pull the record for each unique ID where the DateConfirmed is the most recent. However, in some instances I have an ID that has more than one record with the same 'most recent' DateConfirmed. As a tie breaker in those situations, I'd like to go with the record that has the most fields populated. So in this example, the result I'm looking for would pull back:

12345 Bugs Bunny 123 Hello Street Topeka KS 22222 12/1/1985
54321 Elmer Fudd 678 Penny Lane Detroit MI 88888 7/5/2010

The catch is I have to use inline SQL from a VB6 app to do it. Any help would be appreciated.

 

[SkipVought]

hi,

(all fields are text):

where the DateConfirmed is the most recent.

Since your DateConfirmed is text, you must convert the text to a REAL DATE in your query in order to determine most recent or anything else relating to relative date values.

Your current SQL would help.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DerPflug]

Sorry, the DateConfirmed field is indeed a date/time field.

 

[MajP]

Can you confirm you limitations. If this was in Access you could build a simple UDF function and maybe do it inline with vb functions to determine how many fields are filled out. But if you are limited to pure Sql queries without any vb or UDF functions than not sure if it can be done.

 

[vbajock]

Can you add a numeric field to the table? If you can, then you could use a series of update statements to increase a counter value in the added field. In this example, I've added a field called NullCounter:

UPDATE tblCust SET tblCust.NullCounter = 0


UPDATE tblCust SET tblCust.NullCounter = 7
WHERE (((tblCust.Addr1) Is Null) AND ((tblCust.Addr2) Is Null) AND ((tblCust.City) Is Null) AND ((tblCust.FacilityName) Is Null) AND ((tblCust.Region) Is Null) AND ((tblCust.PostalCode) Is Null) AND ((tblCust.Attn) Is Null))


UPDATE tblCust SET tblCust.NullCounter = 6
WHERE (((tblCust.Addr2) Is Null) AND ((tblCust.City) Is Null) AND ((tblCust.FacilityName) Is Null) AND ((tblCust.Region) Is Null) AND ((tblCust.PostalCode) Is Null) AND ((tblCust.Attn) Is Null))

UPDATE tblCust SET tblCust.NullCounter = 5
WHERE (((tblCust.City) Is Null) AND ((tblCust.FacilityName) Is Null) AND ((tblCust.Region) Is Null) AND ((tblCust.PostalCode) Is Null) AND ((tblCust.Attn) Is Null))

etc

Make sure your queries are in descending order.

Then pull your records as MAX date and MIN [NullCounter]

 

[vbajock]

Note, you are going to have to add to those sql's a WHERE nullcounter =0 to keep from overwriting values.