Home » SQL & PL/SQL » SQL & PL/SQL » How to retrive only Duplicate rows from table-reg
How to retrive only Duplicate rows from table-reg [message #216271] Thu, 25 January 2007 23:27 Go to next message
sambaponnekanti
Messages: 9
Registered: January 2007
Location: Hyderabad
Junior Member



Dear All

With reference of the above subject how to select the duplicate rows from table using sql statement.

Regards
Samba
Re: How to retrive only Duplicate rows from table-reg [message #216273 is a reply to message #216271] Fri, 26 January 2007 00:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Asked & answered many, many times in this forum & GOOGLE.
Re: How to retrive only Duplicate rows from table-reg [message #216292 is a reply to message #216271] Fri, 26 January 2007 03:26 Go to previous messageGo to next message
vijaykasi
Messages: 11
Registered: January 2007
Location: London
Junior Member
This question answered many times in this forum.

Pls find below your answer.

SELECT
EMP_ID,
FROM
EMP A
WHERE
rowid >
(SELECT min(rowid) FROM EMP B
WHERE
B.EMP_ID = A.EMP_ID
);
Re: How to retrive only Duplicate rows from table-reg [message #216315 is a reply to message #216271] Fri, 26 January 2007 06:37 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
To select all but the first of each set of duplicates:

SELECT * FROM yourtable
WHERE rowid IN
      ( SELECT LEAD(rowid) OVER (PARTITION BY keycolumns ORDER BY NULL)
        FROM   yourtable );

[Updated on: Fri, 26 January 2007 06:38]

Report message to a moderator

Previous Topic: Last visit on location
Next Topic: SQL Types and Procedure
Goto Forum:
  


Current Time: Mon Dec 05 04:33:20 CST 2016

Total time taken to generate the page: 0.07445 seconds