|
|
|
Junior Member
 
Group: Forum Members
Last Login: 9/7/2009 5:50:22 AM
Posts: 20,
Visits: 40
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/18/2007 8:56:00 PM
Posts: 5,
Visits: 1
|
|
Hi Meem,
Try this:
SELECT EMPID, EMPNAME, EMPSSN FROM EMPLOYEE GROUP BY 1,2,3
It should remove all duplicates.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 9/7/2009 5:50:22 AM
Posts: 20,
Visits: 40
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/18/2007 8:56:00 PM
Posts: 5,
Visits: 1
|
|
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
-----------------------------------------------------------
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 9/7/2009 5:50:22 AM
Posts: 20,
Visits: 40
|
|
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.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/18/2007 8:56:00 PM
Posts: 5,
Visits: 1
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/7/2009 8:33:47 AM
Posts: 2,
Visits: 3
|
|
|
Perhaps I'm missing the point , but could you not just use select distinct?
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 2/18/2008 4:01:01 AM
Posts: 30,
Visits: 14
|
|
volatile table need more priviledge than just create tables :-)
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/18/2007 8:56:00 PM
Posts: 5,
Visits: 1
|
|
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?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/10/2009 6:28:52 PM
Posts: 505,
Visits: 546
|
|
|
As a matter of fact, the user doesn't require create table privileges to create volatile table.
|
|
|
|