Max date in sql query issue

[chris-uk-lad]

Hi all,

I know how to get the max date per ID with:

select max(DDATE) from Table1 where ID in ('AA1', 'AA2', 'AA3', 'AA4')
group by ID

However, how can i retrieve all columns within the table based only on the max date? I know, by including all the tables column headings, it will ignore the group by thus give me unwanted records.

Many Thanks

 

[Brianwarnock]

I'm sorry but your requirement is not clear to me, I'm getting old.

Do you want all columns per max date per ID?

Brian

 

[chris-uk-lad]

I'm sorry but your requirement is not clear to me, I'm getting old.

Do you want all columns per max date per ID?

Brian

Thats correct, so where i have 3 occurances, i want just 1 which holds the max date of the 3 aswell as all other data in the record.

 

[jdraw]

Hi all,

I know how to get the max date per ID with:

select max(DDATE) from Table1 where ID in ('AA1', 'AA2', 'AA3', 'AA4')
group by ID

However, how can i retrieve all columns within the table based only on the max date? I know, by including all the tables column headings, it will ignore the group by thus give me unwanted records.

Many Thanks

Here is an example of a similar situation (my tables and fields) --
use two queries.
Max(serviceDate) by vehicle id by Customer

1. Query to get the columns you want queryName:CustomerVehicleMaintenance

SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName
, VInventory.VehicleId, VMaintenance.ServiceDate
FROM (Customers INNER JOIN VInventory ON Customers.CustomerID = VInventory.CustomerId) 
INNER JOIN VMaintenance ON VInventory.VehicleId = VMaintenance.VehicleID;

Result:

Customer ID Company Name Contact Name VehicleId ServiceDate
ALFKI Alfreds Futterkiste Maria Anders 1 5/27/2006
ALFKI Alfreds Futterkiste Maria Anders 1 6/2/2006
ALFKI Alfreds Futterkiste Maria Anders 1 11/8/2007
ALFKI Alfreds Futterkiste Maria Anders 2 3/9/2007
ALFKI Alfreds Futterkiste Maria Anders 2 3/15/2007
ALFKI Alfreds Futterkiste Maria Anders 1 7/21/2007
ALFKI Alfreds Futterkiste Maria Anders 3 11/15/2008
ANATR Ana Trujillo Emparedados y helados Ana Trujillo 4 10/3/2007
ANATR Ana Trujillo Emparedados y helados Ana Trujillo 4 3/21/2008

2. Query the first query to get the Max values you need.
queryName:CustomerVehicleMaintenanceMaxServicedate

SELECT CustomerVehicleMaintenance.CustomerID, CustomerVehicleMaintenance.CompanyName
, CustomerVehicleMaintenance.VehicleId
, Max(CustomerVehicleMaintenance.ServiceDate) AS MaxOfServiceDate
FROM CustomerVehicleMaintenance
GROUP BY CustomerVehicleMaintenance.CustomerID
, CustomerVehicleMaintenance.CompanyName
, CustomerVehicleMaintenance.VehicleId;

Result:

Customer ID Company Name VehicleId MaxOfServiceDate
ALFKI Alfreds Futterkiste 1 11/8/2007
ALFKI Alfreds Futterkiste 2 3/15/2007
ALFKI Alfreds Futterkiste 3 11/15/2008
ANATR Ana Trujillo Emparedados y helados 4 3/21/2008

 

[Brianwarnock]

Still not clear as there appear to be 2 scenarios, the simple one which I feel sure that you know is to take the query you have and join it to the ID and DDate of the table to pull all the data you require.

The second is that having got the possible 4 answers to that query you only want the data for the one with the max ddate.
Ignoring
what happens if 2 or more max DDate are the same
Subqueries, as I'm not a whizz at those

I suspect that you will need
A query of query1 to find Max of MaxofDDate
a query to join that back to to query1 to get the ID
and finally join query3 back to the table to get all of the data.

Brian

 

[chris-uk-lad]

Well i have just tried playing with:

SELECT *
FROM Table1, (SELECT max(DDATE) as maxdate, ID
FROM Table1 
GROUP BY ID) maxresults
WHERE Table1.ID= maxresults.ID
AND Table1.DDATE= maxresults.maxdate 
AND Table1.ID in ('AA1', 'AA2', 'AA3', 'AA4', 'AD4', 'AD6', 'AD8', 'AE9');

Which appears to work but misses out 1 of the records.

Thoughts?

 

[Brianwarnock]

As I said I'm not a whizz at subqueries but shouldn't the first From be From maxresults

Brian

No thats not right I'll have another think

 

[Brianwarnock]

I'm not good at air code I need data to play with but I would have put the ID filter in the sub query.

Brian

 

[chris-uk-lad]

I'm not good at air code I need data to play with but I would have put the ID filter in the sub query.

Brian

Yes have tried that, the initial query as the bracketted select (correct number of results) then wrapping it in the select * statement yet it still doesnt produce correct resuilts..

select * from Table1, (select ID,max(DDATE) as maxdate from Table1 
where ID in 'AA1', 'AA2', 'AA3', 'AA4', 'AD4', 'AD6', 'AD8', 'AE9' GROUP BY ID)maxresults
where Table1.ID= maxresults.ID
and Table1.DDate= maxresults.maxdate;

 

[chris-uk-lad]

solved it now, issue with selection criteria

Thanks for all your input

 

[Brianwarnock]

I'm glad its working but would love to know what was wrong as the code looked ok

Brian

 

[jdraw]

Yes have tried that, the initial query as the bracketted select (correct number of results) then wrapping it in the select * statement yet it still doesnt produce correct resuilts..

select * from Table1, (select ID,max(DDATE) as maxdate from Table1 
where ID in 'AA1', 'AA2', 'AA3', 'AA4', 'AD4', 'AD6', 'AD8', 'AE9' GROUP BY ID)maxresults
where Table1.ID= maxresults.ID
and Table1.DDate= maxresults.maxdate;

I don't know all the fields in your Table1, but I don't believe you can use the *
when you want to do aggregate functions (Max in this case).

Here is code I think will work , but I have added a name and city field to show all fields can be presented. As I said, I don't know the names of your fields. Replace all occurences of name,city with your field names.

SELECT id,name,city,  max(DDATE) as maxdate
FROM 
[SELECT id,name,city, DDATE
FROM Table2
WHERE 
 Table2.ID in ('AA1', 'AA2', 'AA3', 'AA4', 'AD4', 'AD6', 'AD8', 'AE9')]. as[xyz]
 GROUP BY ID,name,city

I was busy testing/responding and didn't see you had solved this.

Great!

Could you please post your solution?

 

[Brianwarnock]

I don't know all the fields in your Table1, but I don't believe you can use the *
when you want to do aggregate functions (Max in this case).

He used max in the subquery not the main query where he used *

Brian