distinct date

[mrhankey]

hi there,

looking to get some help.

i have created a view which pulls together from different product tables a review date.

the thing is that a client could have review dates for the same product and i need it to only display the most recent review date.

i tried max(ReviewDate) but did not give the results i needed

results are at moment for example

       product ReviewDate ClientID

       product1 2014-01-20  1
       product2 2014-01-25  2
       product1 2013-01-20  1

so basically it should only show clientid 2 and one of clientid 1 which would be the latest one desc which would be 2014-01-20 and exlude the 2013 entry.

anyone know how to do this in a query?

thanks

[diafol]

You could do this:

SELECT product, ReviewDate, Client_ID FROM (SELECT * FROM tablename ORDER BY ReviewDate DESC) AS t GROUP BY Client_ID

But there may be a more succinct way.

[rch1231]

Hello

What you are looking for is to show the record for the most recent date that a product review was completed. The trick for doing this is grouping. You could use this:

SELECT product, Client_ID, MAX(ReviewDate) 
FROM tablename
group by product, Client_ID;