Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    64

    Question Unanswered: hiding or removing column output from select statement

    I'm executing the following...

    select COL1, min(COL2) from TABLE group by COL1

    the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.

    I was hoping a simple
    "delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"

    would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?

    this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.

    tia

    a

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the subselect identifies the min COL2 primary key for a set of dupe COL1 values

    so just delete based on the primary key

    delete from table where COL2 not in (select min(COL2) from TABLE group by COL1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    64

    Talking Doh

    Too obvious!

    Thanks.

    a

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •