Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] duplicate data in a table

Expand Messages
  • Palaniappan V
    select distinct name,email from table name ... -- Regards V.Palaniappan Thymus Solution Limited No. 45, Thiru Complex, Pantheon Road , Egmore, Chennai 600 008.
    Message 1 of 25 , Oct 20, 2008
    • 0 Attachment
      select distinct name,email from table name



      On Mon, Oct 20, 2008 at 5:58 PM, O S <owaiz_vaiyani@...> wrote:
      >
      > Hi
      >
      > How can i return duplicate records from same table?
      >
      > suppose i have tbl(name,email) (no primarykey)
      >
      > name1 email1
      > name2 email2
      > name1 email1
      >
      > is there a way to find this?
      >
      >
      >
      > thanks
      > __________________________________________________________
      > Catch up on all the latest celebrity gossip
      > http://clk.atdmt.com/GBL/go/115454061/direct/01/
      >
      > [Non-text portions of this message have been removed]
      >
      >



      --
      Regards
      V.Palaniappan

      Thymus Solution Limited
      No. 45, Thiru Complex,
      Pantheon Road ,
      Egmore,
      Chennai 600 008.
      Tamil Nadu , India .
      Ph - (O)+91 - 44- 42148446
      (M) +91 - 99944762362
    • John Warner
      That will return name1 and name2, note name2 in the sample data is not a duplicate. John Warner
      Message 2 of 25 , Oct 20, 2008
      • 0 Attachment
        That will return name1 and name2, note name2 in the 'sample' data is not a
        duplicate.

        John Warner




        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Palaniappan V
        > Sent: Monday, October 20, 2008 9:55 AM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: Re: [SQLQueriesNoCode] duplicate data in a table
        >
        >
        > select distinct name,email from table name
        >
        >
        >
        > On Mon, Oct 20, 2008 at 5:58 PM, O S
        > <owaiz_vaiyani@...> wrote:
        > >
        > > Hi
        > >
        > > How can i return duplicate records from same table?
        > >
        > > suppose i have tbl(name,email) (no primarykey)
        > >
        > > name1 email1
        > > name2 email2
        > > name1 email1
        > >
        > > is there a way to find this?
        > >
        > >
        > >
        > > thanks __________________________________________________________
        > > Catch up on all the latest celebrity gossip
        > > http://clk.atdmt.com/GBL/go/115454061/direct/01/
        > >
        > > [Non-text portions of this message have been removed]
        > >
        > >
        >
        >
        >
        > --
        > Regards
        > V.Palaniappan
        >
        > Thymus Solution Limited
        > No. 45, Thiru Complex,
        > Pantheon Road ,
        > Egmore,
        > Chennai 600 008.
        > Tamil Nadu , India .
        > Ph - (O)+91 - 44- 42148446
        > (M) +91 - 99944762362
        >
        > ------------------------------------
        >
        > Yahoo! Groups Links
        >
        >
        >
      • Yano, Grant
        select name, email, count(*) from tbl group by name, email having count(*) 1 order by name, email any records that have a count greater than one means it is
        Message 3 of 25 , Oct 20, 2008
        • 0 Attachment
          select name, email, count(*)
          from tbl
          group by name, email
          having count(*) > 1
          order by name, email

          any records that have a count greater than one means it is a duplicate
          record in the same table.

          Enjoy.


          ________________________________

          From: SQLQueriesNoCode@yahoogroups.com
          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of O S
          Sent: Monday, October 20, 2008 5:28 AM
          To: sqlqueriesnocode@yahoogroups.com
          Subject: [SQLQueriesNoCode] duplicate data in a table




          Hi

          How can i return duplicate records from same table?

          suppose i have tbl(name,email) (no primarykey)

          name1 email1
          name2 email2
          name1 email1

          is there a way to find this?



          thanks
          __________________________________________________________
          Catch up on all the latest celebrity gossip
          http://clk.atdmt.com/GBL/go/115454061/direct/01/
          <http://clk.atdmt.com/GBL/go/115454061/direct/01/>

          [Non-text portions of this message have been removed]






          [Non-text portions of this message have been removed]
        • peternilsson42
          ... It s not actually necessary to select count(*). ... It works just fine in Oracle too. ... Yes, but it s not something that I d use. Much more useful are
          Message 4 of 25 , Oct 20, 2008
          • 0 Attachment
            "John Warner" <john@...> wrote:
            > Max Parmenter wrote:
            > > John has beaten me to it! Here's an Oracle example as
            > > well as SQL Server...
            > >
            > > SQL Server:
            ...
            > > SELECT dup_name, COUNT(*)

            It's not actually necessary to select count(*).

            > > FROM duplicate_test
            > > GROUP BY dup_name
            > > HAVING COUNT(*) > 1

            It works just fine in Oracle too.

            > > Oracle:
            ...
            > > SELECT dup_name, COUNT(*)
            > > FROM duplicate_test
            > > HAVING count(*) > 1
            > > GROUP BY dup_name;
            >
            > My Oracle is extremely weak, does it support the HAVING
            > clause before the GROUP BY clause?

            Yes, but it's not something that I'd use.

            Much more useful are Oracle's analytical functions...

            with
            blah as
            (
            select 'name1' name, 'email1' email from dual union all
            select 'name2' name, 'email2' email from dual union all
            select 'name1' name, 'email1' email from dual
            )
            select name,
            email,
            row_number() over (partition by name, email
            order by null) "n",
            count(*) over (partition by name, email) "of"
            from blah b
            order by 1, 2, "n", "of";

            NAME EMAIL n of
            ----- ------ - --
            name1 email1 1 2
            name1 email1 2 2
            name2 email2 1 1

            --
            Peter
          • Luiz Ribeiro
            Hi. Try this: SELECT * FROM table_name WHERE ROWID IN (SELECT MIN (ROWID) FROM table_name GROUP BY column_name_1, column_name_2 HAVING COUNT (*) 1); Regards,
            Message 5 of 25 , Oct 21, 2008
            • 0 Attachment
              Hi. Try this:

              SELECT * FROM table_name WHERE ROWID IN
              (SELECT MIN (ROWID) FROM table_name
              GROUP BY column_name_1, column_name_2
              HAVING COUNT (*) > 1);


              Regards,



              Luiz



              [Non-text portions of this message have been removed]
            • Max Parmenter
              Isn t this just a massive overkill to find duplicates?! I am genuinely interested Peter. What s the benefit of using your method over the simpler HAVING and
              Message 6 of 25 , Oct 21, 2008
              • 0 Attachment
                Isn't this just a massive overkill to find duplicates?! I am genuinely
                interested Peter. What's the benefit of using your method over the simpler
                HAVING and GROUP BY clauses?

                Best wishes,

                Max

                -----Original Message-----
                From: SQLQueriesNoCode@yahoogroups.com
                [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of peternilsson42
                Sent: 21 October 2008 06:16
                To: SQLQueriesNoCode@yahoogroups.com
                Subject: [SQLQueriesNoCode] Re: duplicate data in a table



                "John Warner" <john@...> wrote:
                > Max Parmenter wrote:
                > > John has beaten me to it! Here's an Oracle example as
                > > well as SQL Server...
                > >
                > > SQL Server:
                ....
                > > SELECT dup_name, COUNT(*)

                It's not actually necessary to select count(*).

                > > FROM duplicate_test
                > > GROUP BY dup_name
                > > HAVING COUNT(*) > 1

                It works just fine in Oracle too.

                > > Oracle:
                ....
                > > SELECT dup_name, COUNT(*)
                > > FROM duplicate_test
                > > HAVING count(*) > 1
                > > GROUP BY dup_name;
                >
                > My Oracle is extremely weak, does it support the HAVING
                > clause before the GROUP BY clause?

                Yes, but it's not something that I'd use.

                Much more useful are Oracle's analytical functions...

                with
                blah as
                (
                select 'name1' name, 'email1' email from dual union all
                select 'name2' name, 'email2' email from dual union all
                select 'name1' name, 'email1' email from dual
                )
                select name,
                email,
                row_number() over (partition by name, email
                order by null) "n",
                count(*) over (partition by name, email) "of"
                from blah b
                order by 1, 2, "n", "of";

                NAME EMAIL n of
                ----- ------ - --
                name1 email1 1 2
                name1 email1 2 2
                name2 email2 1 1

                --
                Peter







                E-mail message checked by Spyware Doctor (6.0.0.386)
                Database version: 5.10940
                http://www.pctools.com/spyware-doctor-antivirus/
                <http://www.pctools.com/uk/spyware-doctor-antivirus/>






                E-mail message checked by Spyware Doctor (6.0.0.386)
                Database version: 5.10940
                http://www.pctools.com/uk/spyware-doctor-antivirus/


                [Non-text portions of this message have been removed]
              • vigoudhaya
                TABLE:PATIENT COLUMN:REGISTATIONNO,NAME,CITY,COUNTRY AVOID DUPLICATE ROW SETP 1:--    SELECT * INTO DUPTABLE FROM(SELECT * FROM PATIENT GROUP BY  
                Message 7 of 25 , Oct 21, 2008
                • 0 Attachment
                  TABLE:PATIENT
                  COLUMN:REGISTATIONNO,NAME,CITY,COUNTRY
                  AVOID DUPLICATE ROW
                  SETP 1:--
                     SELECT * INTO DUPTABLE FROM(SELECT * FROM PATIENT GROUP BY
                    REGISTATIONNO,NAME,CITY,COUNTRY HAVING COUNT(*) >1 ) AS GAN
                  STEP 2:
                     DELETE  FROM PATIENT WHERE REGISTATIONNO IN (SELECT REGISTATIONNO FROM PATIENT GROUP BY
                    REGISTATIONNO HAVING COUNT(*) >1 ) AS GAN
                  STEP 3:
                     INSERT PATIENT SELECT * FROM DUPTABLE

                  Regards,
                  UdhayaGanesh Pachiyappan,
                  SQL Database Administrator,
                  Apollo Speciality Hospital,
                  Chennai-35.



                  Send free SMS to your Friends on Mobile from your Yahoo! Messenger. Download Now! http://messenger.yahoo.com/download.php

                  [Non-text portions of this message have been removed]
                • peternilsson42
                  ... In the simple case, yes, definitly! ... I didn t mean to imply a recommendation for the simple case. I only meant to say that Oracle s analytical functions
                  Message 8 of 25 , Oct 21, 2008
                  • 0 Attachment
                    "Max Parmenter" <max.parmenter@...> wrote:
                    > peternilsson42 wrote:
                    > > <snip>
                    > > Much more useful are Oracle's analytical functions...
                    > >
                    > > with
                    > > blah as
                    > > (
                    > > select 'name1' name, 'email1' email from dual union all
                    > > select 'name2' name, 'email2' email from dual union all
                    > > select 'name1' name, 'email1' email from dual
                    > > )
                    > > select name,
                    > > email,
                    > > row_number() over (partition by name, email
                    > > order by null) "n",
                    > > count(*) over (partition by name, email) "of"
                    > > from blah b
                    > > order by 1, 2, "n", "of";
                    > >
                    > > NAME EMAIL n of
                    > > ----- ------ - --
                    > > name1 email1 1 2
                    > > name1 email1 2 2
                    > > name2 email2 1 1
                    >
                    > Isn't this just a massive overkill to find duplicates?!

                    In the simple case, yes, definitly!

                    > I am genuinely interested Peter. What's the benefit of
                    > using your method over the simpler HAVING and GROUP BY
                    > clauses?

                    I didn't mean to imply a recommendation for the simple
                    case. I only meant to say that Oracle's analytical
                    functions are a lot more interesting than Oracle
                    allowing you to swap the group by and having clauses. :-)

                    But... suppose you have a table like:

                    cust_no number(8) not null,
                    eff_dt date not null,
                    status_tx varchar2(1) not null,
                    create_ts timestamp not null

                    For each cust_no, there can be multiple rows with the same
                    eff_dt. They are differentiated by the create_ts. [Not a
                    greate design, but sometimes it's what you're faced with!]

                    Now suppose you want to find the items where there are
                    multiple entries for the most recent effective date, and
                    you want to show the first and last status for that
                    effective date, in one row per relevant cust_no.

                    Here's one way to do it in normal SQL...

                    select td.cust_no,
                    td.eff_dt,
                    tmin.status_tx,
                    tmax.status_tx
                    from (select t.cust_no,
                    t.eff_dt,
                    min(create_ts) min_ts,
                    max(create_ts) max_ts
                    from sample_table t
                    where eff_dt =
                    (select max(t2.eff_dt)
                    from sample_table t2
                    where t2.cust_no = t.cust_no)
                    group by
                    t.cust_no,
                    t.eff_dt
                    having count(*) > 1) td
                    --
                    join sample_table tmin
                    on tmin.cust_no = td.cust_no
                    and tmin.eff_dt = td.eff_dt
                    and tmin.create_ts = td.min_ts
                    --
                    join sample_table tmax
                    on tmax.cust_no = td.cust_no
                    and tmax.eff_dt = td.eff_dt
                    and tmax.create_ts = td.max_ts;

                    Here's one way in Oracle...

                    select t.cust_no,
                    t.eff_dt,
                    max(case when rn_min = 1 then t.status_tx end),
                    max(case when rn_max = 1 then t.status_tx end)
                    from (select t.cust_no,
                    t.eff_dt,
                    t.status_tx,
                    max(t.eff_dt) over
                    (partition by t.cust_no) max_eff_dt,
                    row_number() over
                    (partition by t.cust_no, t.eff_dt
                    order by t.create_ts asc) rn_min,
                    row_number() over
                    (partition by t.cust_no, t.eff_dt
                    order by t.create_ts desc) rn_max,
                    count(*) over
                    (partition by t.cust_no, t.eff_dt) ce_cnt
                    from sample_table t) t
                    where t.eff_dt = t.max_eff_dt
                    and t.ce_cnt > 1
                    group by
                    t.cust_no,
                    t.eff_dt

                    The difference is, the former has 4 full table scans,
                    the latter has only 1!

                    Now consider if you want to show the latest two statuses
                    (where there are two or more) for the most recent eff_dt
                    for each relevant cust_no. I think the plain SQL will
                    become a little more complex, whereas the Oracle query
                    actually becomes simpler...

                    select t.cust_no,
                    t.eff_dt,
                    max(case when rn = 1 then t.status_tx end),
                    max(case when rn = 2 then t.status_tx end)
                    from (select t.cust_no,
                    t.eff_dt,
                    t.status_tx,
                    max(t.eff_dt) over
                    (partition by t.cust_no) max_eff_dt,
                    row_number() over
                    (partition by t.cust_no, t.eff_dt
                    order by t.create_ts desc) rn,
                    count(*) over
                    (partition by t.cust_no, t.eff_dt) ce_cnt
                    from sample_table t) t
                    where t.eff_dt = t.max_eff_dt
                    and t.ce_cnt > 1
                    group by
                    t.cust_no,
                    t.eff_dt

                    --
                    Peter
                  • Max Parmenter
                    Thanks Peter for the time you spent replying - I ve learned something new as a result and I m grateful :) Best wishes, Max ... From:
                    Message 9 of 25 , Oct 22, 2008
                    • 0 Attachment
                      Thanks Peter for the time you spent replying - I've learned something new as
                      a result and I'm grateful :)

                      Best wishes,

                      Max


                      -----Original Message-----
                      From: SQLQueriesNoCode@yahoogroups.com
                      [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of peternilsson42
                      Sent: 22 October 2008 00:16
                      To: SQLQueriesNoCode@yahoogroups.com
                      Subject: [SQLQueriesNoCode] Re: duplicate data in a table



                      "Max Parmenter" <max.parmenter@...> wrote:
                      > peternilsson42 wrote:
                      > > <snip>
                      > > Much more useful are Oracle's analytical functions...
                      > >
                      > > with
                      > > blah as
                      > > (
                      > > select 'name1' name, 'email1' email from dual union all
                      > > select 'name2' name, 'email2' email from dual union all
                      > > select 'name1' name, 'email1' email from dual
                      > > )
                      > > select name,
                      > > email,
                      > > row_number() over (partition by name, email
                      > > order by null) "n",
                      > > count(*) over (partition by name, email) "of"
                      > > from blah b
                      > > order by 1, 2, "n", "of";
                      > >
                      > > NAME EMAIL n of
                      > > ----- ------ - --
                      > > name1 email1 1 2
                      > > name1 email1 2 2
                      > > name2 email2 1 1
                      >
                      > Isn't this just a massive overkill to find duplicates?!

                      In the simple case, yes, definitly!

                      > I am genuinely interested Peter. What's the benefit of
                      > using your method over the simpler HAVING and GROUP BY
                      > clauses?

                      I didn't mean to imply a recommendation for the simple
                      case. I only meant to say that Oracle's analytical
                      functions are a lot more interesting than Oracle
                      allowing you to swap the group by and having clauses. :-)

                      But... suppose you have a table like:

                      cust_no number(8) not null,
                      eff_dt date not null,
                      status_tx varchar2(1) not null,
                      create_ts timestamp not null

                      For each cust_no, there can be multiple rows with the same
                      eff_dt. They are differentiated by the create_ts. [Not a
                      greate design, but sometimes it's what you're faced with!]

                      Now suppose you want to find the items where there are
                      multiple entries for the most recent effective date, and
                      you want to show the first and last status for that
                      effective date, in one row per relevant cust_no.

                      Here's one way to do it in normal SQL...

                      select td.cust_no,
                      td.eff_dt,
                      tmin.status_tx,
                      tmax.status_tx
                      from (select t.cust_no,
                      t.eff_dt,
                      min(create_ts) min_ts,
                      max(create_ts) max_ts
                      from sample_table t
                      where eff_dt =
                      (select max(t2.eff_dt)
                      from sample_table t2
                      where t2.cust_no = t.cust_no)
                      group by
                      t.cust_no,
                      t.eff_dt
                      having count(*) > 1) td
                      --
                      join sample_table tmin
                      on tmin.cust_no = td.cust_no
                      and tmin.eff_dt = td.eff_dt
                      and tmin.create_ts = td.min_ts
                      --
                      join sample_table tmax
                      on tmax.cust_no = td.cust_no
                      and tmax.eff_dt = td.eff_dt
                      and tmax.create_ts = td.max_ts;

                      Here's one way in Oracle...

                      select t.cust_no,
                      t.eff_dt,
                      max(case when rn_min = 1 then t.status_tx end),
                      max(case when rn_max = 1 then t.status_tx end)
                      from (select t.cust_no,
                      t.eff_dt,
                      t.status_tx,
                      max(t.eff_dt) over
                      (partition by t.cust_no) max_eff_dt,
                      row_number() over
                      (partition by t.cust_no, t.eff_dt
                      order by t.create_ts asc) rn_min,
                      row_number() over
                      (partition by t.cust_no, t.eff_dt
                      order by t.create_ts desc) rn_max,
                      count(*) over
                      (partition by t.cust_no, t.eff_dt) ce_cnt
                      from sample_table t) t
                      where t.eff_dt = t.max_eff_dt
                      and t.ce_cnt > 1
                      group by
                      t.cust_no,
                      t.eff_dt

                      The difference is, the former has 4 full table scans,
                      the latter has only 1!

                      Now consider if you want to show the latest two statuses
                      (where there are two or more) for the most recent eff_dt
                      for each relevant cust_no. I think the plain SQL will
                      become a little more complex, whereas the Oracle query
                      actually becomes simpler...

                      select t.cust_no,
                      t.eff_dt,
                      max(case when rn = 1 then t.status_tx end),
                      max(case when rn = 2 then t.status_tx end)
                      from (select t.cust_no,
                      t.eff_dt,
                      t.status_tx,
                      max(t.eff_dt) over
                      (partition by t.cust_no) max_eff_dt,
                      row_number() over
                      (partition by t.cust_no, t.eff_dt
                      order by t.create_ts desc) rn,
                      count(*) over
                      (partition by t.cust_no, t.eff_dt) ce_cnt
                      from sample_table t) t
                      where t.eff_dt = t.max_eff_dt
                      and t.ce_cnt > 1
                      group by
                      t.cust_no,
                      t.eff_dt

                      --
                      Peter







                      E-mail message checked by Spyware Doctor (6.0.0.386)
                      Database version: 5.10960
                      http://www.pctools.com/spyware-doctor-antivirus/
                      <http://www.pctools.com/uk/spyware-doctor-antivirus/>






                      E-mail message checked by Spyware Doctor (6.0.0.386)
                      Database version: 5.10960
                      http://www.pctools.com/uk/spyware-doctor-antivirus/


                      [Non-text portions of this message have been removed]
                    • Gautam Das
                      hi there, i am trying to study with example queries but couldn t find google. can anybody send me any link or any file which have sql or mysql queries. Thanks
                      Message 10 of 25 , Oct 23, 2008
                      • 0 Attachment
                        hi there,
                        i am trying to study with example queries but couldn't find google.
                        can anybody send me any link or any file which have sql or mysql queries.

                        Thanks
                        Gotu
                        _________________________________________________________________
                        Searching for the best deals on travel? Visit MSN Travel.
                        http://in.msn.com/coxandkings

                        [Non-text portions of this message have been removed]
                      • John Warner
                        http://www.devshed.com/c/a/MySQL/Beginning-MySQL-Tutorial/ John Warner ... queries.
                        Message 11 of 25 , Oct 25, 2008
                        • 0 Attachment
                          http://www.devshed.com/c/a/MySQL/Beginning-MySQL-Tutorial/

                          John Warner


                          > -----Original Message-----
                          > From: SQLQueriesNoCode@yahoogroups.com
                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Gautam Das
                          > Sent: Thursday, October 23, 2008 12:55 PM
                          > To: sqlqueriesnocode@yahoogroups.com
                          > Subject: [SQLQueriesNoCode] sql queries examples
                          >
                          > hi there,
                          > i am trying to study with example queries but couldn't find google.
                          > can anybody send me any link or any file which have sql or mysql
                          queries.
                          >
                          > Thanks
                          > Gotu
                          > _______________________________________________________________
                          > __
                          > Searching for the best deals on travel? Visit MSN Travel.
                          > http://in.msn.com/coxandkings
                          >
                          > [Non-text portions of this message have been removed]
                          >
                          >
                          > ------------------------------------
                          >
                          > Yahoo! Groups Links
                          >
                          >
                          >
                        • mahwish
                          www.w3schools.com   is an excellent link in this regard best wishes ahead [Non-text portions of this message have been removed]
                          Message 12 of 25 , Oct 25, 2008
                          • 0 Attachment
                            www.w3schools.com
                             
                            is an excellent link in this regard
                            best wishes ahead




                            [Non-text portions of this message have been removed]
                          • mahwish
                            www.3schools.com an excellent link in this regard [Non-text portions of this message have been removed]
                            Message 13 of 25 , Oct 26, 2008
                            • 0 Attachment
                              www.3schools.com
                              an excellent link in this regard




                              [Non-text portions of this message have been removed]
                            Your message has been successfully submitted and would be delivered to recipients shortly.