SQL Question to eliminate records

[kwagner1]

Greetings,
I am using the following SQL (via External Data option in Excel) to pull records from my database. I need a way to eliminate the "duplicate" ID's

<code>
SELECT appid, UpdateDate FROM dbo.apmv_HistoryAttributeChange a WHERE UpdateDate between '01/01/2009' AND '04/01/2009'
</code>

My result from the above SQL is something like this:
ID32978 1/29/2009 7:16
ID32978 3/11/2009 7:16
ID32982 1/16/2009 7:16
ID32982 1/29/2009 7:16
ID32982 3/15/2009 7:16

I want to remove the duplicate ID's and return just the most current ID record - resulting in this:
ID32978 3/11/2009 7:16
ID32982 3/15/2009 7:16

Anyone know how to change my SQL?

thanks!

 

[Andy B]

SELECT appid, MAX(UpdateDate) AS MaxDate
FROM dbo.apmv_HistoryAttributeChange
GROUP BY appid

 

[Andy B]

Sorry, missed the date range. I will re-post for you shortly.

 

[Andy B]

I assume the date range can be applied to the maximum date as this is the only one you are bringing through, therefore your SQL script is:

SELECT apmv_HistoryAttributeChange.appid, Max(apmv_HistoryAttributeChange.UpdateDate) AS 'MaxDate'
FROM "200_HawkTest".dbo.apmv_HistoryAttributeChange apmv_HistoryAttributeChange
GROUP BY apmv_HistoryAttributeChange.appid
HAVING (Max(apmv_HistoryAttributeChange.UpdateDate) Between {ts '2009-03-11 00:00:00'} And {ts '2009-03-15 00:00:00'})

 

[Andy B]

Note: Slight oversight, replace "200 Hawk Test" with the name of your SQL database, this was my test database!

Good Luck

 

[kwagner1]

thanks Andy. I will check it out.

 

[Fazza]

Be careful.

The proposed SQL

SELECT appid, MAX(UpdateDate) AS [UpdateDate]
FROM dbo.apmv_HistoryAttributeChange
GROUP BY appid
HAVING Max(UpdateDate) BETWEEN '01/01/2009' AND '04/01/2009'

is different from

SELECT appid, MAX(UpdateDate) AS [UpdateDate]
FROM dbo.apmv_HistoryAttributeChange
WHERE UpdateDate BETWEEN '01/01/2009' AND '04/01/2009'
GROUP BY appid

(I don't know which you want, but do want to point out the possibilities.)

The former returns only most recent UpdateDates if they are in the date range. The latter filters only records in the date range before then giving the latest update date in that restricted range (there may be later update dates).

regards, Fazza

 

[ddadmin2009]

Good point Fazza. I was also observing that -- I think for the requirement in the question looks like the below code should be perfect because this fetches all the records between the date range and then does grouping or max..

SELECT appid, MAX(UpdateDate) AS [UpdateDate]
FROM dbo.apmv_HistoryAttributeChange
WHERE UpdateDate BETWEEN '01/01/2009' AND '04/01/2009'
GROUP BY appid

 

[Andy B]

Yes there is a distinction, thanks for pointing that out Fazza. For some reason when I tried the other method first in SQL it didnt work which I thought was because I was not returning the individual dates within the query however this turned out not to be the case I think I had a format problem, it worked fine when I copied and pasted yours! .

The devil is in the detail!