I'm going to create a unique constraint on one of the tables I'm transferring to ElevateDB, problem is there are some duplicates. The important bits of the structure are

_ID: autoinc
_Plan: varchar
_Company: varchar
_Contact: varchar

I can get a list of the duplicated entries using

select _ID, _plan,_company,_contact, Count(1) as fred INTO "Memory\Temp" from projectdetails
group by _plan,_company,_contact
having count(1) > 1;

Mostly its 2 offs but there are a few 4 offs

what I want to do next is delete the ones in each set with the lowest _ID. And here's where I'm stuck

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:CD5D7330-C5F8-4C72-82D4-7D2CE330DCF8@news.elevatesoft.com...
> I'm going to create a unique constraint on one of the tables I'm
> transferring to ElevateDB, problem is there are some duplicates. The
> important bits of the structure are
>
> _ID: autoinc
> _Plan: varchar
> _Company: varchar
> _Contact: varchar
>
> I can get a list of the duplicated entries using
>
> select _ID, _plan,_company,_contact, Count(1) as fred INTO "Memory\Temp"
> from projectdetails
> group by _plan,_company,_contact
> having count(1) > 1;
>

I'm not sure the above gets you the duplicates.  I run this SQL on a small
table called fred (don't ask) with a structure similar to yours,  should do
the trick.

select a, b, sum(1) mysum into memory\temp from fred
group by a, b
having mysum > 1;

select max(id) maxid, a, b into memory\temp2 from fred f
join memory\temp t on (t.a = f.a) and (t.b = f.b)
group by a, b;

delete from fred f
join memory\temp2 m on f.a = m.a and f.b = m.b
where f.id <> m.maxid;

Robert

Robert



>I'm not sure the above gets you the duplicates. I run this SQL on a small
>table called fred (don't ask) with a structure similar to yours, should do
>the trick.

I don't need to ask - its one I use a lot just that in my case it never makes it through to production I also have lots of things called qaz.

I'll try your code - thanks.

Roy Lambert

Robert


Worked nicely - thanks again

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:22A49E50-5B7E-4FFA-B971-84A63537B83D@news.elevatesoft.com...
> Robert
>
>
> Worked nicely - thanks again
>

Sure. This issue comes up periodically, is there some repository of
solutions / tips where the answers can be stored? Sort of what Schkolnik has
in his website?

Robert

Robert wrote:

>
> Sure. This issue comes up periodically, is there some repository of solutions / tips where the answers can be stored? Sort of what Schkolnik has in his website?
>

Well, personally, I use Steve Forbes' Knowledge Base for storing the little bits of code that save my life.
At one time there was talk of a co-operative effort to contribute items to be made available as a downloadable import - but I heard no more.  

Malcolm

--

Robert,

<< Sure. This issue comes up periodically, is there some repository of
solutions / tips where the answers can be stored? Sort of what Schkolnik has
in his website? >>

This just came up on the EDB newsgroups also.  I'll see about adding
something where everyone can add bits like this to a central repository.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>This just came up on the EDB newsgroups also. I'll see about adding
>something where everyone can add bits like this to a central repository.

Nice idea. I'd like to suggest appointing a couple of wardens as well who have the power to remove any tips that don't work.

Roy Lambert