All Forums Database
Meem 24 posts Joined 05/07
20 May 2007
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 : EMPLOYEEEMPID EMPNAME EMPSSN************************1 Jack 555-55-55552 Joe 555-56-55553 Fred 555-57-55554 Mike 555-58-55555 Cathy 555-59-55556 Lisa 555-70-55551 Jack 555-55-55554 Mike 555-58-55555 Cathy 555-59-55556 Lisa 555-70-55556 Lisa 555-70-5555. .... ............ .... ............ .... ...........Note :I dont have the rights to Create any TableI dont have the rights to Add a new Column to the tableIm not suppose to use any other utilsI 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) AWHERE B.EMPID = A.EMPIDGROUP BY 1,2,3;CheersMeem

Mitrich 5 posts Joined 05/07
20 May 2007

Hi Meem,Try this:SELECT EMPID, EMPNAME, EMPSSN FROM EMPLOYEE GROUP BY 1,2,3It should remove all duplicates.

Meem 24 posts Joined 05/07
20 May 2007

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

Mitrich 5 posts Joined 05/07
20 May 2007

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-----------------------------------------------------------

Meem 24 posts Joined 05/07
20 May 2007

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.

Mitrich 5 posts Joined 05/07
21 May 2007

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.htmBest regards,Mitrich

PhilP 2 posts Joined 05/07
25 May 2007

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

Luckyhansh 30 posts Joined 08/06
31 May 2007

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

Mitrich 5 posts Joined 05/07
04 Jun 2007

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?

joedsilva 505 posts Joined 07/05
04 Jun 2007

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

You must sign in to leave a comment.