Query for selecting NON DUPLICATE ELEMENTS in a table

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • swami
    #1

    Query for selecting NON DUPLICATE ELEMENTS in a table

    What is the query for selecting non duplicate elements

    for eg:

    no name age
    1 siva 28
    2 blair 32
    3 mano 28

    i want to select blair which hasn't got any duplicate elements in age
    column.

    Thx in advance
    Tags: None
    • Lennart
      #2
      Re: Query for selecting NON DUPLICATE ELEMENTS in a table

      On 28 Okt, 17:25, swami <sivaswamim...@ gmail.comwrote:
      What is the query for selecting non duplicate elements
      >
      for eg:
      >
      no   name age
      1     siva    28
      2     blair    32
      3     mano  28
      >
      i want to select blair which hasn't got any duplicate elements in age
      column.
      >
      One possibility:

      db2 "with T (no, name, age) as (values (1,'siva',28), (2,'blair',32),
      (3,'mano',28)) select T.* from T where age in (select age from T group
      by age having count(1) = 1)"

      NO NAME AGE
      ----------- ----- -----------
      2 blair 32

      1 record(s) selected.

      /Lennart

        Comment

        • Lennart
          #3
          Re: Query for selecting NON DUPLICATE ELEMENTS in a table

          On 28 Okt, 17:25, swami <sivaswamim...@ gmail.comwrote:
          What is the query for selecting non duplicate elements
          >
          for eg:
          >
          no   name age
          1     siva    28
          2     blair    32
          3     mano  28
          >
          i want to select blair which hasn't got any duplicate elements in age
          column.
          >
          Thx in advance
          Not sure what happened to my post, here is one attempt:

          db2 "with T (no, name, age) as (values (1,'siva',28), (2,'blair',32),
          (3,'mano',28)) select T.* from T where age in (select age from T group
          by age having count(1) = 1)"

          NO NAME AGE
          ----------- ----- -----------
          2 blair 32

          1 record(s) selected.

          /Lennart

            Comment

            • swami
              #4
              Re: Query for selecting NON DUPLICATE ELEMENTS in a table

              Is this going to be such complex for retrieval of simple data
              isn't there any function for this ? just like distinct ?
              or can it be added to newer versions of db2 ?

              On Oct 28, 8:16 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
              On 28 Okt, 17:25, swami <sivaswamim...@ gmail.comwrote:
              >
              What is the query for selecting non duplicate elements
              >
              for eg:
              >
              no   name age
              1     siva    28
              2     blair    32
              3     mano  28
              >
              i want to select blair which hasn't got any duplicate elements in age
              column.
              >
              Thx in advance
              >
              Not sure what happened to my post, here is one attempt:
              >
               db2 "with T (no, name, age) as (values (1,'siva',28), (2,'blair',32),
              (3,'mano',28)) select T.* from T where age in (select age from T group
              by age having count(1) = 1)"
              >
              NO          NAME  AGE
              ----------- ----- -----------
                        2 blair          32
              >
                1 record(s) selected.
              >
              /Lennart

                Comment

                • jefftyzzer
                  #5
                  Re: Query for selecting NON DUPLICATE ELEMENTS in a table

                  On Oct 29, 4:43 am, swami <sivaswamim...@ gmail.comwrote:
                  Is this going to be such complex for retrieval of simple data
                  isn't there any function for this ? just like distinct ?
                  or can it be added to newer versions of db2 ?
                  >
                  On Oct 28, 8:16 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
                  >
                  On 28 Okt, 17:25, swami <sivaswamim...@ gmail.comwrote:
                  >
                  What is the query for selecting non duplicate elements
                  >
                  for eg:
                  >
                  no   name age
                  1     siva    28
                  2     blair    32
                  3     mano  28
                  >
                  i want to select blair which hasn't got any duplicate elements in age
                  column.
                  >
                  Thx in advance
                  >
                  Not sure what happened to my post, here is one attempt:
                  >
                   db2 "with T (no, name, age) as (values (1,'siva',28), (2,'blair',32),
                  (3,'mano',28)) select T.* from T where age in (select age from T group
                  by age having count(1) = 1)"
                  >
                  NO          NAME  AGE
                  ----------- ----- -----------
                            2 blair          32
                  >
                    1 record(s) selected.
                  >
                  /Lennart
                  >
                  >
                  Another way:

                  SELECT
                  NO,
                  NAME,
                  AGE
                  FROM
                  (
                  SELECT
                  NO,
                  NAME,
                  AGE,
                  ROW_NUMBER() OVER (PARTITION BY AGE) RN
                  FROM
                  DEMOG_TABLE
                  ) DT
                  WHERE
                  RN = 1;

                  DISTINCT won't work (at least in a straightforward way) because you
                  can't use it to simultaneously retrieve several columns and apply the
                  DISTINCT on just a subset of them.

                  --Jeff

                    Comment

                    • Will Honea@teranews.com
                      #6
                      Re: Query for selecting NON DUPLICATE ELEMENTS in a table

                      jefftyzzer wrote:
                      On Oct 29, 4:43 am, swami <sivaswamim...@ gmail.comwrote:
                      >Is this going to be such complex for retrieval of simple data
                      >isn't there any function for this ? just like distinct ?
                      >or can it be added to newer versions of db2 ?
                      >>
                      >On Oct 28, 8:16 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
                      >>
                      On 28 Okt, 17:25, swami <sivaswamim...@ gmail.comwrote:
                      >>
                      What is the query for selecting non duplicate elements
                      >>
                      for eg:
                      >>
                      no   name age
                      1     siva    28
                      2     blair    32
                      3     mano  28
                      >>
                      i want to select blair which hasn't got any duplicate elements in age
                      column.
                      >>
                      Thx in advance
                      >>
                      Not sure what happened to my post, here is one attempt:
                      >>
                      db2 "with T (no, name, age) as (values (1,'siva',28), (2,'blair',32),
                      (3,'mano',28)) select T.* from T where age in (select age from T group
                      by age having count(1) = 1)"
                      >>
                      NO          NAME  AGE
                      ----------- ----- -----------
                      2 blair          32
                      >>
                      1 record(s) selected.
                      >>
                      /Lennart
                      >>
                      >>
                      >
                      Another way:
                      >
                      SELECT
                      NO,
                      NAME,
                      AGE
                      FROM
                      (
                      SELECT
                      NO,
                      NAME,
                      AGE,
                      ROW_NUMBER() OVER (PARTITION BY AGE) RN
                      FROM
                      DEMOG_TABLE
                      ) DT
                      WHERE
                      RN = 1;
                      >
                      DISTINCT won't work (at least in a straightforward way) because you
                      can't use it to simultaneously retrieve several columns and apply the
                      DISTINCT on just a subset of them.
                      Your solution runs a good deal faster here - I've used both to update a
                      database I maintain for a non-profit group membership list. It's amazing
                      how many duplicate names appear in successive generations attending one
                      school

                      --
                      Will Honea
                      ** Posted from http://www.teranews.com **

                        Comment

                        • jefftyzzer
                          #7
                          Re: Query for selecting NON DUPLICATE ELEMENTS in a table

                          On Oct 29, 10:19 am, "Will Ho...@teranews. com" <who...@yahoo.c om>
                          wrote:
                          jefftyzzer wrote:
                          On Oct 29, 4:43 am, swami <sivaswamim...@ gmail.comwrote:
                          Is this going to be such complex for retrieval of simple data
                          isn't there any function for this ? just like distinct ?
                          or can it be added to newer versions of db2 ?
                          >
                          On Oct 28, 8:16 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
                          >
                          On 28 Okt, 17:25, swami <sivaswamim...@ gmail.comwrote:
                          >
                          What is the query for selecting non duplicate elements
                          >
                          for eg:
                          >
                          no   name age
                          1     siva    28
                          2     blair    32
                          3     mano  28
                          >
                          i want to select blair which hasn't got any duplicate elements in age
                          column.
                          >
                          Thx in advance
                          >
                          Not sure what happened to my post, here is one attempt:
                          >
                          db2 "with T (no, name, age) as (values (1,'siva',28), (2,'blair',32),
                          (3,'mano',28)) select T.* from T where age in (select age from T group
                          by age having count(1) = 1)"
                          >
                          NO          NAME  AGE
                          ----------- ----- -----------
                          2 blair          32
                          >
                          1 record(s) selected.
                          >
                          /Lennart
                          >
                          Another way:
                          >
                          SELECT
                              NO,
                              NAME,
                              AGE
                          FROM
                              (
                              SELECT
                                  NO,
                                  NAME,
                                  AGE,
                                  ROW_NUMBER() OVER (PARTITION BY AGE) RN
                              FROM
                                  DEMOG_TABLE
                              ) DT
                          WHERE
                              RN = 1;
                          >
                          DISTINCT won't work (at least in a straightforward way) because you
                          can't use it to simultaneously retrieve several columns and apply the
                          DISTINCT on just a subset of them.
                          >
                          Your solution runs a good deal faster here - I've used both to update a
                          database I maintain for a non-profit group membership list.  It's amazing
                          how many duplicate names appear in successive generations attending one
                          school
                          >
                          --
                          Will Honea
                          ** Posted fromhttp://www.teranews.co m**
                          Swami:

                          It occurs to me that I may not have answered the right question. If
                          what you want is the first instance, duplicated or otherwise, then you
                          can use the query I originally posted. If, however, what you want is
                          to retrieve just those rows that are not/never were duplicated over
                          the age column, then this query is likely what you're after:

                          SELECT
                          NO,
                          NAME,
                          AGE
                          FROM
                          (
                          SELECT
                          NO,
                          NAME,
                          AGE,
                          COUNT() OVER (PARTITION BY AGE) CNT
                          FROM
                          DEMOG_TABLE
                          ) DT
                          WHERE
                          CNT = 1;

                          Will: thanks for your comment. Glad to know you're doing your part to
                          ensure high-quality data!

                          Regards,

                          --Jeff

                            Comment

                            • jefftyzzer
                              #8
                              Re: Query for selecting NON DUPLICATE ELEMENTS in a table

                              On Oct 29, 10:19 am, "Will Ho...@teranews. com" <who...@yahoo.c om>
                              wrote:
                              jefftyzzer wrote:
                              On Oct 29, 4:43 am, swami <sivaswamim...@ gmail.comwrote:
                              Is this going to be such complex for retrieval of simple data
                              isn't there any function for this ? just like distinct ?
                              or can it be added to newer versions of db2 ?
                              >
                              On Oct 28, 8:16 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
                              >
                              On 28 Okt, 17:25, swami <sivaswamim...@ gmail.comwrote:
                              >
                              What is the query for selecting non duplicate elements
                              >
                              for eg:
                              >
                              no   name age
                              1     siva    28
                              2     blair    32
                              3     mano  28
                              >
                              i want to select blair which hasn't got any duplicate elements in age
                              column.
                              >
                              Thx in advance
                              >
                              Not sure what happened to my post, here is one attempt:
                              >
                              db2 "with T (no, name, age) as (values (1,'siva',28), (2,'blair',32),
                              (3,'mano',28)) select T.* from T where age in (select age from T group
                              by age having count(1) = 1)"
                              >
                              NO          NAME  AGE
                              ----------- ----- -----------
                              2 blair          32
                              >
                              1 record(s) selected.
                              >
                              /Lennart
                              >
                              Another way:
                              >
                              SELECT
                                  NO,
                                  NAME,
                                  AGE
                              FROM
                                  (
                                  SELECT
                                      NO,
                                      NAME,
                                      AGE,
                                      ROW_NUMBER() OVER (PARTITION BY AGE) RN
                                  FROM
                                      DEMOG_TABLE
                                  ) DT
                              WHERE
                                  RN = 1;
                              >
                              DISTINCT won't work (at least in a straightforward way) because you
                              can't use it to simultaneously retrieve several columns and apply the
                              DISTINCT on just a subset of them.
                              >
                              Your solution runs a good deal faster here - I've used both to update a
                              database I maintain for a non-profit group membership list.  It's amazing
                              how many duplicate names appear in successive generations attending one
                              school
                              >
                              --
                              Will Honea
                              ** Posted fromhttp://www.teranews.co m**
                              Swami:

                              It occurs to me that I may not have answered the right question. If
                              what you want is the first instance, duplicated or otherwise, then you
                              can use the query I originally posted. If, however, what you want is
                              to retrieve just those rows that are not/never were duplicated over
                              the age column, then this query is likely what you're after:

                              SELECT
                              NO,
                              NAME,
                              AGE
                              FROM
                              (
                              SELECT
                              NO,
                              NAME,
                              AGE,
                              COUNT(*) OVER (PARTITION BY AGE) CNT
                              FROM
                              DEMOG_TABLE
                              ) DT
                              WHERE
                              CNT = 1;

                              Will:

                              Thanks for your comment. Glad to know you're doing your part to
                              ensure high-quality data!

                              Regards,

                              --Jeff

                                Comment

                                Previous template Next
                                Working...