# SQL max date problem



## skooby19_oc (Apr 22, 2009)

Hi I am trying to figure out how to query for the lastest date. Here is an example of what my customer price table looks like, with the desired results in a bigger font.


```
Part     Price        Date           Customer
[B][SIZE=3]A1     $1.00    2009-09-30   XYZco[/SIZE][/B]
[B][SIZE=3]A2     $5.00    2009-05-02   XYZco[/SIZE][/B]
A3       $2.00      2008-06-04       XYZco
A1       $1.50      2009-02-24       XYZco 
[B][SIZE=3]A3     $2.20    2008-09-04   XYZco[/SIZE][/B]
```
Whenever I try this code, I get all parts. When all I want is one record per part and that record having the latest date. If I remove the price from the select statement, it works. I can't figure out how to filter the duplicate part records and still show the price. 


```
select Part, Price, max(Date) as datemax,  Customer   
from price_table                              
where customer='XYZco'                  
group by Part, Price, Customer                       
order by Part
```


----------



## OBP (Mar 8, 2005)

I am not sure what type of SQL you have, but this works in Access Query SQL.
Create a query with just the part and the date, set to Grouping for the Part and then Max(Date) as you currently have it.
Create a second query based on the first one and join it to the original table via Part to Part and Max(Date) to Date.
That should then only pick up the last date for each part.
The problem with what you currently have is that it is also Grouping by price.


----------



## skooby19_oc (Apr 22, 2009)

The application that I'm using this SQL statemtent is an ASP page querying an IBM DB2 server. 

I'm going to try the method that you suggested. Wrapping another SQL statement around a query that is just getting the part and latest date.

Would I put the part/date query in the select line or in the where line??

Thanks for your help


----------



## DoubleHelix (Dec 10, 2004)

Looks like an odd database schema. No primary key, and all the customers are the same. That'll cause a problem when grouping by customer.


----------



## OBP (Mar 8, 2005)

I would suggest the Where line.


----------



## JimmySeal (Sep 25, 2007)

Please give this a try:


```
SELECT p_t.* FROM price_table AS p_t INNER JOIN
   (SELECT Part, max(Date) AS datemax
   FROM price_table                              
   WHERE customer='XYZco'                  
   GROUP BY Part, Price) AS max_prices
ON max_prices.Part = p_t.Part AND max_prices.datemax = p_t.Date
ORDER BY p_t.Part
```


----------

