SQL Question to eliminate records

kwagner1

Active Member
Joined
Jun 10, 2003
Messages
445
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!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Andy B

Active Member
Joined
Mar 26, 2009
Messages
336
SELECT appid, MAX(UpdateDate) AS MaxDate
FROM dbo.apmv_HistoryAttributeChange
GROUP BY appid
 

Andy B

Active Member
Joined
Mar 26, 2009
Messages
336
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

Active Member
Joined
Mar 26, 2009
Messages
336

ADVERTISEMENT

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

Good Luck
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

Be careful.

The proposed SQL
Rich (BB code):
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
Rich (BB code):
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

New Member
Joined
Apr 7, 2009
Messages
35
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

Active Member
Joined
Mar 26, 2009
Messages
336
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top