Locating and removing identical rows (except one column)

  1. 10 months ago

    Brian O

    10 Jan 2019 Pre-Release Testers Calgary, AB

    I've got a list of requests that I place into a table.
    There is only one unique column in the table. rownumber.
    There will be no NULLs in any rows.

    However after the table has been populated I want to remove duplicate rows. (where every column in two or more rows are the same except for the row number.\
    (psuedo code!)

    Select distinct * from Table; was what first came to mind but then the row number is always different,

    So how can i, without having to type every column name except the row number, write that in SQL?
    Select distinct (* except rownumber) from Table;

    Any other thought on duplicate record removal?
    I would think this isn't an uncommon thing for DB managers to face.

    I've found a few SQL statements on giggle but i think being able to express a list of all but one column in the database may be what I'm looking for.
    (Assuming also that the statement i've expressed actually do what i think they should, but I haven't tested them yet,)

  2. Markus W

    10 Jan 2019 #JeSuisHuman New Zealand, Auc...
    Edited 10 months ago

    Which database are you talking about? And is “rownumber” the RowID or do you have a separate RowID? Because the unique RowID is not being involved in distinct

  3. Beatrix W

    10 Jan 2019 Pre-Release Testers, Third Party Store Europe (Germany)

    You could get all fields of the table and then make a dynamic SQL.

  4. Hal G

    11 Jan 2019 Pre-Release Testers, Xojo Pro, XDC Speakers CampSoftware.com

    Beatrix had a good idea. In MySQL you can query INFORMATION_SCHEMA.COLUMNS to get a list of all the column names and the loop thru each making a comma delimited list while skipping the rownumber. :) Then with the comma-delimited list, you can query with DISTINCT. :)

    https://www.mssqltips.com/sqlservertutorial/183/informationschemacolumns/

or Sign Up to reply!