max() throwing up group by errors, please help :D

[HeavenCore]

hello, i have rows of data with duplicates EXCEPT the id which is unique, i am try to select a row for each max date of each duplicate set: for example with the follwoing rows:

1----------08/10/2007----------Delivery----------Dorothy Haynes
2----------28/09/2007----------Delivery----------Dorothy Haynes
3----------08/10/2007----------Delivery----------Dorothy Kittrick
4----------28/09/2007----------Delivery----------Dorothy Kittrick
5----------01/10/2007----------Delivery----------Edith Gill
6----------08/10/2007----------Delivery----------Edith Gill
7----------28/09/2007----------Delivery----------Edith Gill

i need to return data like:

1----------08/10/2007----------Delivery----------Dorothy Haynes
3----------08/10/2007----------Delivery----------Dorothy Kittrick
6----------08/10/2007----------Delivery----------Edith Gill

cutting out duplicates using the latest date as the final result row.

i tried:

select vw_Requests.Request_ID, Pat_Fullname, vw_Requests.Request_Created 
from vw_Requests
WHERE vw_Requests.Request_Type_ID = 3 and vw_Requests.Shop_ID = 012 AND vw_Requests.Run_ID Is Null 
group by vw_Requests.Pat_Fullname, vw_Requests.Request_Created
having vw_Requests.Request_Created = max(vw_Requests.Request_Created)
order by vw_Requests.Request_Created Desc

but i just get error:

Column 'vw_Requests.Request_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

now i understand why the error occurs, but grouping by the id returns hundreds of rows and thus does not work, any ideas?

Best regards

HC

[iburyak]

Try this:

select max(vw_Requests.Request_ID), Pat_Fullname, max(vw_Requests.Request_Created)
from vw_Requests
WHERE vw_Requests.Request_Type_ID = 3 and vw_Requests.Shop_ID = 012 AND vw_Requests.Run_ID Is Null 
group by vw_Requests.Pat_Fullname
order by vw_Requests.Request_Created Desc

Good Luck.

[HeavenCore]

thankyou for your reply. However that has two issues,

1. The order by clause throws up the error 'Column name 'vw_Requests.Request_Created' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.'
2. if i remove the order by clause the query executes, however the data returned is incorrect, as i will now explain:

note in the image below the data we are selecting from (in the excel book) and the actual selected data on the left, you will see it has not selected the row with the higher date?

any ideas?

[HeavenCore]

UPDATE: whatever idiot wrote the view i am selecting from has declared the datetime field in the view as a varchar, so max() is maxing the string, not as data, which explains why is select the higher date based on the 2 at the beinging of the dd/mm/yyyy

So i'm guessing i will have to do max(cast(date as satetime) although the varchar only has dd/mm/yyyy so i may need to use convert().

best get reading up on my date convert codes :D

[iburyak]

You are right.
Just if you still want order by just do this:

Order by 3

Order by column positions....


Good Luck.