Query Help

Database
Enthusiast

Query Help

Hi All,
I would like to eliminate duplicates and make the following table with unique records. I wish to have a query to solve such issues.

TableName : EMPLOYEE

EMPID EMPNAME EMPSSN
************************
1 Jack 555-55-5555
2 Joe 555-56-5555
3 Fred 555-57-5555
4 Mike 555-58-5555
5 Cathy 555-59-5555
6 Lisa 555-70-5555
1 Jack 555-55-5555
4 Mike 555-58-5555
5 Cathy 555-59-5555
6 Lisa 555-70-5555
6 Lisa 555-70-5555
. .... ...........
. .... ...........
. .... ...........

Note :
I dont have the rights to Create any Table
I dont have the rights to Add a new Column to the table
Im not suppose to use any other utils

I tried a Query using Row_Number() which makes the records unique but again i donno how to remove duplicates, Kindly help!

SELECT B.EMPID,
B.EMPNAME,
B.EMPSSN,
MIN(A.RID)
FROM
EMPLOYEE B,
(SELECT EMPID,
ROW_NUMBER() OVER (ORDER BY EMPID ASC) RID
FROM EMPLOYEE) A
WHERE
B.EMPID = A.EMPID
GROUP BY 1,2,3;

Cheers
Meem
9 REPLIES
Enthusiast

Re: Query Help

Hi Meem,

Try this:

SELECT EMPID, EMPNAME, EMPSSN FROM EMPLOYEE GROUP BY 1,2,3

It should remove all duplicates.

Enthusiast

Re: Query Help

Hi Mitrich,
Thanks for your reply!
It did worked but im in need of a Delete query which would produce the result similar to the one you mentioned.

Regards
Enthusiast

Re: Query Help

Try this
----------------------------------
create volatile multiset table test123 (
id integer
,name varchar(20)
) on commit preserve rows
;
insert into test123 (1, 'aaaa');
insert into test123 (1, 'aaaa');
insert into test123 (2, 'bbbb');
insert into test123 (2, 'bbbb');
insert into test123 (3, 'cccc');

insert into test123
select id, '##'||name as name
from test123
qualify row_number() over(partition by id order by id) = 1
;
delete from test123 where not name like '##%'
;
update test123 set name = substring(name from 3)
;
select * from test123
-----------------------------------------------------------

Enthusiast

Re: Query Help

Hi Mitrich,

Your script works!! I cant implement it bcoz i dont hav the rights to INSERT any new rows, can you advise please?

Regards.
Enthusiast

Re: Query Help

I don't see any other ways how to solve your problem.
Ask permission either to create table or insert rows.

This problem was disscused here:
www.teradataforum.com/teradata/20040505_182346.htm

Best regards,
Mitrich
Fan

Re: Query Help

Perhaps I'm missing the point , but could you not just use select distinct?
Enthusiast

Re: Query Help

volatile table need more priviledge than just create tables :-)

Enthusiast

Re: Query Help

I used it only to show how it can be implemented.
In any case could you tell what kind of priviledges it require except create table?
Enthusiast

Re: Query Help

As a matter of fact, the user doesn't require create table privileges to create volatile table.