Possible Query? 2

[jaofos]

Ok, I've been trying for a couple days to get this query to work work.

Here's an example of the data I'm working with:

ID | FirstName | LastName | LastUpdate
----------------------------------------------------
999 | John | Doe | <null>
9999| Jane | Doe | <null>
999 | John | Doe | 12/20/01
9999| Jane | Doe | 12/21/01
999 | John | Doe | 1/7/02
9999| Jane | Doe | 1/6/02

What I need to do is pull the Unique ID's and only pull the latest record (last updated) using ONE query. I'm beginning to think it's impossible without using some sort of code to loop through multi queries (i.e. select distinct ID then loop through the results of that and use SELECT TOP 1 * FROM .. ) So what I'm looking for as a result of the query would be something like:

ID | FirstName | LastName | LastUpdate
----------------------------------------------------
999 | John | Doe | 1/7/02
9999| Jane | Doe | 1/6/02

Can anybody answer me for sure that this can't be done with just ONE query so I can stop wasting my time and look for alternate means to get this done. Any suggestions at all as far as other methods or restructuring the database or different ways I could handle this would be appreciated.

 

[MisterC]

Assuming your table is named &quot;Table1&quot;:

SELECT ID, FirstName, LastName, Max([LastUpdate]) AS LastUpdated
FROM Table1
GROUP BY ID, FirstName, LastName;

--------------
A little knowledge is a dangerous thing.

 

[jaofos]

Oops, forgot to mention I don't want duplicates. I only want the first record.

 

[jaofos]

Nevermind, I got it. Thanks a ton!

 

[jaofos]

Hrm, one thing I just noticed. Once I go over 10 columns pulled from the table, that query no longer works, it starts pulling extra records..

My exact SELECT statement:

SELECT OracleID, FirstName+&quot; &quot;+LastName AS Name, Max([PrefChangeDate]) AS LastPrefChange, Client, TM, Extention, HireDate, Termed, TermDate
FROM AgentPrefs
WHERE PrefChangeDate Is Not Null
GROUP BY OracleID, FirstName, LastName, Client, TM, Extention, HireDate, Termed, TermDate
ORDER BY LastName;

 

[MisterC]

I'm not aware of any 10 column limit on GROUP BY. It should select distinct rows from your data. The only reason it would pull &quot;extra records&quot; is if fields in your group by contain different data. Can you look at the extras and see what the differences are? --------------
A little knowledge is a dangerous thing.

 

[jaofos]

There is really no one difference between the rows that would cause it to pull them. After 10 columns added to the group by, it pulls one, if I had all the columns I want to pull, it ends up pulling all rows where PrefChangeDate != Null.

I'm thinking of restructuring the database to maybe make this query a little easier. Splitting the data into two different tables may help me a little.

 

[MisterC]

If PrefChangeDate can contain a null value, you will get multiple rows. That is because null does not equal null. When you reference the PrefChangeDate field, do it like:

   PrefChangeDate &

&quot;&quot;

 AS PChangeDate

The added &quot;&quot; should fix the Null problem. Give it a shot and see if that does the trick. --------------
A little knowledge is a dangerous thing.

 

[jaofos]

I added that, didn't seem to make a difference

 

[MisterC]

Can you post an example your duplicate records? --------------
A little knowledge is a dangerous thing.

 

[MisterC]

Silly me, I just checked the Access Help. Grouping only works up to 10 fields.
--------------
A little knowledge is a dangerous thing.

 

[jaofos]

Using this Query:

SELECT OracleID, FirstName+&quot; &quot;+LastName AS Name, Max([PrefChangeDate]) AS LastPrefChange, Client, TM, Extention, HireDate, Termed, TermDate
FROM AgentPrefs
WHERE PrefChangeDate Is Not Null
GROUP BY OracleID, FirstName, LastName, Client, TM, Extention, HireDate, Termed, TermDate
ORDER BY LastName;

I get:

OracleID Name LastPrefChange Client TM Extention HireDate Termed TermDate
666 Steve (last) 1/7/2002 15:35 Intraday VCARBO 3010 2/7/2000 0:00 0
6666 Steve (last) 1/10/2002 10:59 Intraday VCARBO 3010 2/7/2000 0:00 0

Adding just one column to make this query:

SELECT OracleID, FirstName+&quot; &quot;+LastName AS Name, Max([PrefChangeDate]) AS LastPrefChange, Client, TM, Extention, HireDate, Termed, TermDate, Phone
FROM AgentPrefs
WHERE PrefChangeDate Is Not Null
GROUP BY OracleID, FirstName, LastName, Client, TM, Extention, HireDate, Termed, TermDate, Phone
ORDER BY LastName;

I get:

OracleID Name LastPrefChange Client TM Extention HireDate Termed TermDate Phone
666 Steve (last) 1/7/2002 15:35 Intraday VCARBO 3010 2/7/2000 0:00 0 662-342-0590
6666 Steve (last) 12/21/2001 0:00 Intraday VCARBO 3010 2/7/2000 0:00 0
6666 Steve (last) 1/10/2002 10:59 Intraday VCARBO 3010 2/7/2000 0:00 0 662-342-0590

 

[jaofos]

Well, guess I need to find a new way to go about getting this data since I can only use GROUP BY for up to 10 columns

 

[johpje]

Hey guys,

I just wanted to point out that any data from the Group By that's not significat to the Group By should be ommitted and added ad a aggregate function. so as Client, TM, Extention, HireDate, Termed and TermDate are specified by id your select should look like:

SELECT OracleID, FirstName+&quot; &quot;+LastName AS Name, Max([PrefChangeDate]) AS LastPrefChange, Max(Client), Max(TM), Max(Extention), Max(HireDate), Max(Termed), Max(TermDate)
FROM AgentPrefs
WHERE PrefChangeDate Is Not Null
GROUP BY OracleID, Firstname, LastName ORDER BY LastName;

The max() function for all these fields will return the correct data because the data is the same for each record within each group specified in the Group BY. Therefore the max() function can be replaced by Min() for instance.

Regards
Johan

 

[MisterC]

Good answer.

 

[jaofos]

Tried that.. still returned more rows than I want

 

[johpje]

duplicate rows or just too many rows?

if you got duplicates, this means the OracleID, Firstname, LastName is not unique.
if you have to many rows you should rephrase your WHERE clause.

 

[MisterC]

I'm looking at your posted results. It appears you are getting multiple records for OracleID 6666 because the Phone# is different.

 

[jaofos]

Multiple rows, not duplicate rows.

Pretty much what I'm trying to do is pull the row containing the latest PrefChangeDate for each unique OracleID.

 

[johpje]

Ok, here's a way you'll only have each id only once:

SELECT a.OracleID, a.FirstName+&quot; &quot;+a.LastName AS Name, a.PrefChangeDate AS LastPrefChange, a.Client, a.TM, a.Extention, a.HireDate, a.Termed, a.TermDate, a.Phone
FROM AgentPrefs a , (SELECT OracleID, Max(PrefChangeDate) FROM AgentPrefs GROUP BY OracleID) b WHERE a.OracleID = b.OracleID and a.LastPrefChange = b.LastPrefChange;

I hope this gives the wanted result.

Regards
Johpje