SQL question

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

    SQL question

    Hi,

    I am pretty new to SQL and I would like to know is it possible to do
    the following task in SQL?

    I have a table containing 2 columns A, B where A is the primary key.
    If the table contains the following data.

    A B
    --- ---
    1 a
    2 b
    3 b
    4 b
    5 c
    6 c
    7 d

    I would like to run a sql statement to get rid of all the rows
    containing duplicated entries of B where the smaller A will get
    deleted. (keep the last row where there is no longer duplicates of B)

    The end result will be

    A B
    --- ---
    1 a
    4 b
    6 c
    7 d

    Thanks,

    --muteki
    Tags: None
    • drew
      #2
      Re: SQL question

      szeming@alumni. washington.edu (muteki) wrote in message news:<f5cee8fb. 0401151833.7ac3 7f7a@posting.go ogle.com>...
      Hi,
      >
      I am pretty new to SQL and I would like to know is it possible to do
      the following task in SQL?
      >
      I have a table containing 2 columns A, B where A is the primary key.
      If the table contains the following data.
      >
      A B
      --- ---
      1 a
      2 b
      3 b
      4 b
      5 c
      6 c
      7 d
      >
      I would like to run a sql statement to get rid of all the rows
      containing duplicated entries of B where the smaller A will get
      deleted. (keep the last row where there is no longer duplicates of B)
      >
      The end result will be
      >
      A B
      --- ---
      1 a
      4 b
      6 c
      7 d
      >
      Thanks,
      >
      --muteki

      SQLselect * from aa;

      A B
      ---------- -
      1 a
      2 b
      3 b
      4 b
      5 c
      6 c
      7 d

      7 rows selected.


      SQLselect max(a) "a",b from aa where b not in
      2 (select b from aa group by b having count(*)>1 ) group by a,b
      3 union
      4 select max(a) "a",b from aa group by b having count(*)>1;

      a B
      ---------- -
      1 a
      4 b
      6 c
      7 d

      SQL>

        Comment

        • VC
          #3
          Re: SQL question

          Hello,

          It's much simpler:

          select * from t1 where (a,b) in (select max(a), b from t1 group by b)


          Rgds.


          "drew" <andrew_toropov @hotmail.comwro te in message
          news:b71c4ae4.0 401160120.644f3 887@posting.goo gle.com...
          szeming@alumni. washington.edu (muteki) wrote in message
          news:<f5cee8fb. 0401151833.7ac3 7f7a@posting.go ogle.com>...
          Hi,

          I am pretty new to SQL and I would like to know is it possible to do
          the following task in SQL?

          I have a table containing 2 columns A, B where A is the primary key.
          If the table contains the following data.

          A B
          --- ---
          1 a
          2 b
          3 b
          4 b
          5 c
          6 c
          7 d

          I would like to run a sql statement to get rid of all the rows
          containing duplicated entries of B where the smaller A will get
          deleted. (keep the last row where there is no longer duplicates of B)

          The end result will be

          A B
          --- ---
          1 a
          4 b
          6 c
          7 d

          Thanks,

          --muteki
          >
          >
          SQLselect * from aa;
          >
          A B
          ---------- -
          1 a
          2 b
          3 b
          4 b
          5 c
          6 c
          7 d
          >
          7 rows selected.
          >
          >
          SQLselect max(a) "a",b from aa where b not in
          2 (select b from aa group by b having count(*)>1 ) group by a,b
          3 union
          4 select max(a) "a",b from aa group by b having count(*)>1;
          >
          a B
          ---------- -
          1 a
          4 b
          6 c
          7 d
          >
          SQL>

            Comment

            • muteki
              #4
              Re: SQL question

              Thanks for the ideas. Instead of querying the end result, how can I
              operate on the table such that it really deletes the duplicates? That
              is, delete everything that is not in your select statement?

              --muteki


              "VC" <boston103@hotm ail.comwrote in message news:<PpQNb.804 72$na.43336@att bi_s04>...
              Hello,
              >
              It's much simpler:
              >
              select * from t1 where (a,b) in (select max(a), b from t1 group by b)
              >
              >
              Rgds.
              >
              >
              "drew" <andrew_toropov @hotmail.comwro te in message
              news:b71c4ae4.0 401160120.644f3 887@posting.goo gle.com...
              szeming@alumni. washington.edu (muteki) wrote in message
              news:<f5cee8fb. 0401151833.7ac3 7f7a@posting.go ogle.com>...
              Hi,
              >
              I am pretty new to SQL and I would like to know is it possible to do
              the following task in SQL?
              >
              I have a table containing 2 columns A, B where A is the primary key.
              If the table contains the following data.
              >
              A B
              --- ---
              1 a
              2 b
              3 b
              4 b
              5 c
              6 c
              7 d
              >
              I would like to run a sql statement to get rid of all the rows
              containing duplicated entries of B where the smaller A will get
              deleted. (keep the last row where there is no longer duplicates of B)
              >
              The end result will be
              >
              A B
              --- ---
              1 a
              4 b
              6 c
              7 d
              >
              Thanks,
              >
              --muteki

              SQLselect * from aa;

              A B
              ---------- -
              1 a
              2 b
              3 b
              4 b
              5 c
              6 c
              7 d

              7 rows selected.


              SQLselect max(a) "a",b from aa where b not in
              2 (select b from aa group by b having count(*)>1 ) group by a,b
              3 union
              4 select max(a) "a",b from aa group by b having count(*)>1;

              a B
              ---------- -
              1 a
              4 b
              6 c
              7 d

              SQL>

                Comment

                Previous template Next
                Working...