Solved

How to remove identify the duplicate with two distinct rows?

Posted on 2009-04-13
2
717 Views
Last Modified: 2013-12-19
Hi,
   I need to identify the duplicate data with two distinct rows. See my data structure below.
NAME      NAME_1   VALUE    START_DATE      END_DATE      FLAG     INDEX
SURI         SE            275        13/12/2005            31/12/2010         B          1
SURI         SE            375                                                                 A          1
SURI         SE            475                                                                 A          1
SURI         SE            275        13/12/2005            31/12/2010         B          2
SURI         SE           375                                                                  A          2
SURI         SE           475                                                                  A          2
SURI         SE           175        13/12/2006            31/12/2010         B          3
SURI         SE           375                                                                  A          3
SURI         SE           475                                                                  A          3

                This is my sample data. Here data are duplicate with two different index column. INDEX 1 and 2 contains same group of combination. So i need to identify any one of duplicate combination(i.e INDEX 1 or 2). Can anyone come up with exact solution?

Thanks
0
Comment
2 Comments
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 250 total points
ID: 24128739
Hi Suriyaraj_Sudalaiappan,

I see combinations that occur more than twice - if you ignore the value in the INDEX column.

This row is unique
SURI         SE     175    13/12/2006      31/12/2010       B         3

These two are duplicate
SURI         SE     275    13/12/2005      31/12/2010       B         1
SURI         SE     275    13/12/2005      31/12/2010       B         2

These three are triplicate
SURI         SE     375                                     A         1
SURI         SE     375                                     A         2
SURI         SE     375                                     A         3

So are these three
SURI         SE     475                                     A         1
SURI         SE     475                                     A         2
SURI         SE     475                                     A         3

Is the below what you where looking for?
--If you want to identify the doubles/triples/... combinations etc 
SELECT NAME, NAME_1, VALUE, START_DATE, END_DATE, FLAG
  FROM yourtable
 GROUP BY NAME, NAME_1, VALUE, START_DATE, END_DATE, FLAG
HAVING COUNT(*) > 1 
-- If you want the individual rows, this assumes you do not have duplicates with the same INDEX value
SELECT *
  FROM yourtable t,
       (SELECT NAME, NAME_1, VALUE, START_DATE, END_DATE, FLAG, MIN(INDEX) as min_index
          FROM yourtable
         GROUP BY NAME, NAME_1, VALUE, START_DATE, END_DATE, FLAG
        HAVING COUNT(*) > 1) v
 WHERE t.NAME        = v.NAME
   AND t.NAME_1      = v.NAME_1
   AND t.VALUE       = v.VALUE
   AND t.START_DATE  = v.START_DATE
   AND t.END_DATE    = v.END_DATE
   AND t.FLAG        = v.FLAG
   AND v.INDEX       > v.min_index

Open in new window

0
 

Accepted Solution

by:
Suriyaraj_Sudalaiappan earned 0 total points
ID: 24773616
Hi,

Below query is giving the exact answer.

SELECT t4.*,
                               MIN(t4.diff_flag) over(PARTITION BY t4.LINE_INDEX) min_flag
                        FROM (SELECT t3.*,
                                        CASE
                                            WHEN lag(ATTR_VALUE_FROM, lag_flag)OVER(ORDER BY LINE_INDEX, START_DATE_ACTIVE, END_DATE_ACTIVE, ATTR_VALUE_FROM) != ATTR_VALUE_FROM AND BASE_ASSOCIATE_FLAG = 'A'
                                            THEN
                                                1
                                            WHEN (lag(ATTR_VALUE_FROM, lag_flag)OVER(ORDER BY LINE_INDEX, START_DATE_ACTIVE, END_DATE_ACTIVE, ATTR_VALUE_FROM) != ATTR_VALUE_FROM
                                                OR lag(START_DATE_ACTIVE, lag_flag) OVER(ORDER BY LINE_INDEX, START_DATE_ACTIVE, END_DATE_ACTIVE, ATTR_VALUE_FROM) != START_DATE_ACTIVE
                                                OR lag(END_DATE_ACTIVE, lag_flag) OVER(ORDER BY LINE_INDEX, START_DATE_ACTIVE, END_DATE_ACTIVE, ATTR_VALUE_FROM) != END_DATE_ACTIVE)
                                                AND BASE_ASSOCIATE_FLAG = 'B'
                                            THEN
                                                1
                                            WHEN lag_flag = 0
                                            THEN
                                                1
                                            ELSE
                                                2
                                        END diff_flag
                                  FROM (SELECT t2.*,
                                                first_value(lag_count_flag) OVER(PARTITION BY LINE_INDEX
                                                                                    ORDER BY LINE_INDEX, START_DATE_ACTIVE, END_DATE_ACTIVE) lag_flag
                                        FROM (SELECT t1.*,
                                                        CASE
                                                            WHEN BASE_ASSOCIATE_FLAG = 'B'
                                                            THEN
                                                                lag(COUNT, 1, 0) OVER(ORDER BY LINE_INDEX, START_DATE_ACTIVE, END_DATE_ACTIVE, ATTR_VALUE_FROM)
                                                        END lag_count_flag
                                                FROM (SELECT t.*,
                                                                COUNT(*) over(PARTITION BY LINE_INDEX) COUNT,
                                                                row_number() over(PARTITION BY NAME, FACTOR_ATTR_CONTEXT, FACTOR_ATTRIBUTE, COMP_OPERATOR, ATTR_VALUE_FROM,
                                                                                               ADJUSTMENT_FACTOR, START_DATE_ACTIVE, END_DATE_ACTIVE, BASE_ASSOCIATE_FLAG
                                                                                  ORDER BY LINE_INDEX) row_number
                                                        FROM xxqp_pam_factor_stg t
                                                        WHERE
                                                            t.process_flag = 1 AND t.request_id = v_conc_req_id) t1) t2) t3) t4
                        ORDER BY t4.LINE_INDEX,
                                 t4.START_DATE_ACTIVE,
                                 t4.ATTR_VALUE_FROM
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now