Need help building query

Using Access and ASP (going to migrate to something more robust when this
is cleaned up):

Given the following information (I know that one card has been assigned
three times)
ID Card Number M_id Added
602 8888177601005601 2 2005-04-11 00:00:00
5101 8888177601005601 11 2006-05-31 00:00:00
5601 8888177601005601 11 2006-05-31 00:00:00

I can run this query and get a list of card numbers that have been
assigned more than once:
SELECT cardnumber, COUNT(cardnumber)
FROM cards
GROUP BY cardnumber
HAVING COUNT(cardnumber) > 1

What I need is a query that will delete the first two records and leave
only one record (the last one entered) remaining. I know I can do this
in ASP (create a new table with a unique index, put the records in it in
reverse order, delete all from the current and put them back in reverse
order again), but I would like to know it there is a query to do it.

Any help or ideas greatly appreciated. TIA.

FYI: When I originally had this account, the table had a unique index on
cardnumber. The client and I had a falling out, and he must have had
problems trying to do duplicate entries, so the new person removed the
index - ergo over 5000 duplicate cards. Now I have the account back, and
the dubious honor of cleaning up the mess.

Client: I can't shoot myself in the foot.
Me: The safety is on.
Client: I can't shoot myself in the foot so I am going to get someone
else.
Me: OK.
Client: I can't shoot myself in the foot.
New AE: The safety has been removed.
Client: I shot myself in the foot. Why does my foot hurt?


--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Re: Need help building query

Adrienne Boswell wrote:
> Using Access and ASP (going to migrate to something more robust when
> this is cleaned up):
>
> Given the following information (I know that one card has been
> assigned three times)
> ID Card Number M_id Added
> 602 8888177601005601 2 2005-04-11 00:00:00
> 5101 8888177601005601 11 2006-05-31 00:00:00
> 5601 8888177601005601 11 2006-05-31 00:00:00
>
> I can run this query and get a list of card numbers that have been
> assigned more than once:
> SELECT cardnumber, COUNT(cardnumber)
> FROM cards
> GROUP BY cardnumber
> HAVING COUNT(cardnumber) > 1
>
> What I need is a query that will delete the first two records and
> leave only one record (the last one entered) remaining. I know I can
> do this in ASP (create a new table with a unique index, put the
> records in it in reverse order, delete all from the current and put
> them back in reverse order again), but I would like to know it there
> is a query to do it.
>
> Any help or ideas greatly appreciated. TIA.
>

Well, this query will return the last entry date for each card:

SELECT cardnumber, max(Added) LastEntryDate
FROM cards
GROUP BY cardnumber

While you could do a series of subqueries, for readability and
maintainability, I would suggest creating a saved query called
LatestEntryDatePerCard using that sql and use it in this query that
retrieves the ID of the last entry for each card (again, creating a saved
query - call it LatestCardIDs):

Select c.ID
From cards c join LatestEntryDatePerCard q
ON c.cardnumber =q.cardnumber and c.Added = q.LastEntryDate

Now this query should delete the earlier entries:

delete c.* from
cards c left join LatestCardIDs q
on c.ID = q.ID
WHERE q.ID Is Null

Probably more efficient would be to do this:

Select c.* into UniqueCards from
cards c join LatestCardIDs q
on c.ID = q.ID

Delete * From cards

Create the unique index on cards, and then:

insert into cards
select * from UniqueCards




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Need help building query

Gazing into my crystal ball I observed "Bob Barrows [MVP]"
writing in
news:u8MpnM1rHHA.1864@TK2MSFTNGP04.phx.gbl:

> Adrienne Boswell wrote:
>> Using Access and ASP (going to migrate to something more robust when
>> this is cleaned up):
>>
>> Given the following information (I know that one card has been
>> assigned three times)
>> ID Card Number M_id Added
>> 602 8888177601005601 2 2005-04-11 00:00:00
>> 5101 8888177601005601 11 2006-05-31 00:00:00
>> 5601 8888177601005601 11 2006-05-31 00:00:00
>>
>> I can run this query and get a list of card numbers that have been
>> assigned more than once:
>> SELECT cardnumber, COUNT(cardnumber)
>> FROM cards
>> GROUP BY cardnumber
>> HAVING COUNT(cardnumber) > 1
>>
>> What I need is a query that will delete the first two records and
>> leave only one record (the last one entered) remaining. I know I can
>> do this in ASP (create a new table with a unique index, put the
>> records in it in reverse order, delete all from the current and put
>> them back in reverse order again), but I would like to know it there
>> is a query to do it.
>>
>> Any help or ideas greatly appreciated. TIA.
>>
>
> Well, this query will return the last entry date for each card:
>
> SELECT cardnumber, max(Added) LastEntryDate
> FROM cards
> GROUP BY cardnumber
>
> While you could do a series of subqueries, for readability and
> maintainability, I would suggest creating a saved query called
> LatestEntryDatePerCard using that sql and use it in this query that
> retrieves the ID of the last entry for each card (again, creating a
> saved query - call it LatestCardIDs):
>
> Select c.ID
> From cards c join LatestEntryDatePerCard q
> ON c.cardnumber =q.cardnumber and c.Added = q.LastEntryDate
>
> Now this query should delete the earlier entries:
>
> delete c.* from
> cards c left join LatestCardIDs q
> on c.ID = q.ID
> WHERE q.ID Is Null
>
> Probably more efficient would be to do this:
>
> Select c.* into UniqueCards from
> cards c join LatestCardIDs q
> on c.ID = q.ID
>
> Delete * From cards
>
> Create the unique index on cards, and then:
>
> insert into cards
> select * from UniqueCards
>
>
>
>

Thanks, Bob - you're always my hero! And Happy Father's Day!

--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Re: Need help building query

Gazing into my crystal ball I observed "Bob Barrows [MVP]" @NOyahoo.SPAMcom> writing in news:u8MpnM1rHHA.1864@TK2MSFTNGP04.phx.gbl:

> Create the unique index on cards, and then:
>
> insert into cards
> select * from UniqueCards
>
>

That's what I ended up doing in the end. Believe me, I had the client here
while I was doing this, and he thought of at least four other more
complicated ways of doing it.

--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share