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

RE: [SQLQueriesNoCode] duplicate data in a table

Expand Messages
  • John Warner
    That will return name1 and name2, note name2 in the sample data is not a duplicate. John Warner
    Message 1 of 25 , Oct 20, 2008
      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 2 of 25 , Oct 20, 2008
        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 3 of 25 , Oct 20, 2008
          "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 4 of 25 , Oct 21, 2008
            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 5 of 25 , Oct 21, 2008
              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 6 of 25 , Oct 21, 2008
                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 7 of 25 , Oct 21, 2008
                  "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 8 of 25 , Oct 22, 2008
                    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 9 of 25 , Oct 23, 2008
                      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 10 of 25 , Oct 25, 2008
                        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 11 of 25 , Oct 25, 2008
                          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 12 of 25 , Oct 26, 2008
                            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.