OraFAQ Forum: SQL & PL/SQL » duplicate record

Home » SQL & PL/SQL » SQL & PL/SQL » duplicate record (oracle 10 g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

duplicate record [message #623985]

Wed, 17 September 2014 01:49

simplesanju
Messages: 36
Registered: July 2008

Member

sample data

table1

col1   col2  col3 col4
1      100   B1   E1W
2      100   B2   E1W
3      300   Y    E1W
4      400   B1   S1S
5      400   B2   S1S
6      500   Y    S1S
7      500   N    S1S

I need records which are having same value in col2 and Col4.

output:

col1   col2  col3 col4
1      100   B1   E1W
2      100   B2   E1W
4      400   B1   S1S
5      400   B2   S1S
6      500   Y    S1S
7      500   N    S1S

Thanks in advance.

sanjana

Lalit: Added code tags.

[Updated on: Wed, 17 September 2014 02:00] by Moderator

Report message to a moderator

Re: duplicate record [message #623986 is a reply to message #623985]

Wed, 17 September 2014 02:11

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

This is the last time I am helping you since you never provide a test case.

SQL> WITH DATA AS(
  2  SELECT 1 COL1,      100 COL2,  'B1' COL3,   'E1W' COL4 FROM DUAL UNION ALL
  3  SELECT 2 ,     100,   'B2',   'E1W' FROM DUAL UNION ALL
  4  SELECT 3  ,    300,   'Y' ,  'E1W' FROM DUAL UNION ALL
  5  SELECT 4  ,    400,   'B1',   'S1S' FROM DUAL UNION ALL
  6  SELECT 5   ,   400,   'B2',   'S1S' FROM DUAL UNION ALL
  7  SELECT 6   ,   500,   'Y' ,   'S1S' FROM DUAL UNION ALL
  8  SELECT 7   ,   500,   'N' ,  'S1S' FROM DUAL)
  9  SELECT b.*
 10  FROM   (SELECT col1,
 11                 col2,
 12                 col3,
 13                 col4,
 14                 Row_number()
 15                   over(
 16                     PARTITION BY col2, col4
 17                     ORDER BY col2, col4) RN
 18          FROM   data) A,
 19         data B
 20  WHERE  A.col2 = B.col2
 21         AND a.col4 = b.col4
 22         AND A.rn = 2
 23
SQL> /

      COL1       COL2 CO COL
---------- ---------- -- ---
         1        100 B1 E1W
         2        100 B2 E1W
         4        400 B1 S1S
         5        400 B2 S1S
         6        500 Y  S1S
         7        500 N  S1S

6 rows selected.

Report message to a moderator

Re: duplicate record [message #623998 is a reply to message #623985]

Wed, 17 September 2014 02:42

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> WITH DATA AS (
  2    SELECT 1 COL1,      100 COL2,  'B1' COL3,   'E1W' COL4 FROM DUAL UNION ALL
  3    SELECT 2 ,     100,   'B2',   'E1W' FROM DUAL UNION ALL
  4    SELECT 3  ,    300,   'Y' ,  'E1W' FROM DUAL UNION ALL
  5    SELECT 4  ,    400,   'B1',   'S1S' FROM DUAL UNION ALL
  6    SELECT 5   ,   400,   'B2',   'S1S' FROM DUAL UNION ALL
  7    SELECT 6   ,   500,   'Y' ,   'S1S' FROM DUAL UNION ALL
  8    SELECT 7   ,   500,   'N' ,  'S1S' FROM DUAL
  9    ),
 10    counted as (
 11      select col1, col2, col3, col4, count(*) over (partition by col2, col4) cnt
 12      from data
 13    )
 14  select col1, col2, col3, col4
 15  from counted
 16  where cnt >= 2
 17  /
      COL1       COL2 CO COL
---------- ---------- -- ---
         1        100 B1 E1W
         2        100 B2 E1W
         4        400 B1 S1S
         5        400 B2 S1S
         6        500 Y  S1S
         7        500 N  S1S

Report message to a moderator

Previous Topic:	Is there a way to select columns with largest column for each specific row
Next Topic:	implementation restriction: cannot reference fields of bulk in-bind table of records error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 01:27:15 CDT 2024