Home » SQL & PL/SQL » SQL & PL/SQL » Delete duplicate rows,shows odd number of rows
Delete duplicate rows,shows odd number of rows [message #216510] Mon, 29 January 2007 00:20 Go to next message
shailesh.jain14
Messages: 6
Registered: December 2006
Location: pune
Junior Member
hi,

1.


i want to delete Delete duplicate rows then what will the sql Query for particular table,

2.
i want to display odd number of rows in the table
when we insert the data in the table there is some sequence of rows
i want to display odd and even number of rows


plz provide the sql query for it

thanks & regards
shailesh jain
Re: Delete duplicate rows,shows odd number of rows [message #216512 is a reply to message #216510] Mon, 29 January 2007 00:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
1: Search the board and google. There are a zillion examples around
2: idem. This will only be possible if you have a column from which you can tell the order of insertions. Without such a column, there is NO order in which the rows are stored in or retrieved from the table.
Re: Delete duplicate rows,shows odd number of rows [message #216691 is a reply to message #216512] Mon, 29 January 2007 23:40 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
select * from ( select a.* ,mod(rownum,2) x from emp a )A where x=1

select * from ( select a.* ,mod(rownum,2) x from emp a )A where x=0


or


select * from ( select a.* ,mod(rownum,2) x from emp a )A where x=&n



Re: Delete duplicate rows,shows odd number of rows [message #216701 is a reply to message #216510] Tue, 30 January 2007 01:16 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
shailesh.jain14 wrote on Mon, 29 January 2007 07:20

when we insert the data in the table there is some sequence of rows
i want to display odd and even number of rows
What do you call "some sequence"? Is it a sequence number, a creation date? Unless you have some column in the table that allows you to identify the order of insertion you will have no chance of getting back an ordered record set.

pavuluri's query will give you NO GUARANTEE of a consistent record set. ROWNUM is assigned as rows are fetched, before a sort is applied. Depending on your storage parameters, DML on the table, ... the order can be entirely different.

As for the deletion of duplicate records, follow Frank's advice and search the board first.

MHE
Previous Topic: Spooling sql*plus file
Next Topic: why can't i select row number 2?
Goto Forum:
  


Current Time: Sun Dec 04 22:31:02 CST 2016

Total time taken to generate the page: 0.13376 seconds