deleting duplicate rows - SQL anywhere

hello all,

is anybody know, in SQL anywhere, how to delete dublicate rows in single query itself..

table design is follows..

name1    age1
----------------
asd          13
asd          14
asd          13

i want the data in table as follows.. (by removing duplicate rows)

name1    age1
----------------
asd         13
asd         14

how we can do this, with single query itself...?
regrds
jothiram
jothirammAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChrisKingCommented:
You can do it in a single statement if you have something to make them distinct (like a timestamp) otherwise you can do it with a loop.

with a timestamp

DELETE yourtable
FROM yourtable a
WHERE EXISTS (SELECT * FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1 AND b.timestamp > a.timestamp)

without a timestamp

SET ROWCOUNT 1

DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1

WHILE @@rowcount > 0
    DELETE yourtable
    FROM yourtable a
    WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1

SET ROWCOUNT 0
ChrisKingCommented:
ChrisKingCommented:
did you have any luck with this ?
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

ahoorCommented:
I'm not sure is Anywhere allows temporary tables, other wise you could try this (given the fact that the 2 columns in the tabel are the only ones).

select distinct *
into   #table
from  your_table

delete your_table

insert into your_table
select * from #table

Not a single statement, but you can't do it in a single statement if you don't have any other columns in the table.
DaniProCommented:
If you have a PK you can use this simple SQL:

DELETE FROM mytable
WHERE EXISTS
 (  SELECT name1, age1, MIN(id) as cf_prog
    FROM mytable TAB02
WHERE ( mytable.name1 = TAB02.name1 ) AND
            ( mytable.age1 = TAB02.age1 )
GROUP BY name1, age1
  HAVING ( count(*) > 1 ) AND
               ( mytable.id <> cf_prog )   )
           ;

ChrisKingCommented:
jothiramm

did your get what you were after ?
ChrisKingCommented:
jothiramm
please return to the question
jothirammAuthor Commented:
sorry..

nothing, satisfies.. and i thought this is not possible in Anywhere..

thanks.
ChrisKingCommented:
why do you say "nothing, satisfies", all of theses solutions will remove duplicate rows
jothirammAuthor Commented:
s

but i want to know, WITHIN single statement, i have to do (pls. refer my question)

table structure is shown samething (shown coloums only there in table)
ChrisKingCommented:
actually you asked for a single "query" and a query can be multiple statements executed in a single batch.

but, this is a single statement

DELETE yourtable
FROM yourtable a
WHERE EXISTS (SELECT * FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1 AND b.timestamp > a.timestamp)

and so is this

DELETE FROM mytable
WHERE EXISTS
 (  SELECT name1, age1, MIN(id) as cf_prog
    FROM mytable TAB02
WHERE ( mytable.name1 = TAB02.name1 ) AND
          ( mytable.age1 = TAB02.age1 )
GROUP BY name1, age1
  HAVING ( count(*) > 1 ) AND
             ( mytable.id <> cf_prog )   )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hredwolfCommented:
1. Via temporary table. As ahoor did.
2.
   a. Add identity column ID
           alter table tbl add id numeric(18,0) identity not null
   b. Execute query:
           delete from tbl where exists
           (select 1 from tbl t2 where tbl.login = t2.login and tbl.age = t2.age and tbl.id > t2.id)
   c. Remove identity column ID
           alter table tbl drop id

P.S. DO NOT NEVER DESIGN TABLE WITHOUT PK!!!!
leonstrykerCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:

Answered ChrisKing

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

leonstryker
EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.