locked
How to remove repeat rows in SQL? RRS feed

  • Question

  • User836525179 posted

    I need to remove repeated records in search result. I have 3 columns (id, worklevel & umobile).

    It looks like below:

    Red places is repeat but I need just one of non-repeat rows in result.

    Monday, February 17, 2020 5:59 PM

Answers

  • User281315223 posted

    i want to remove dublicated row with field "a" and just show one of them

    How do you want to determine _which_ one to show? The DISTINCT keyword may help you with removing one of these, but more likely than not, if they exist, you might consider grouping the results by the value you are experiencing a duplicate of and simply select the first one that occurs:

    SELECT   MIN(id),
             worklevel,
             umobile
    FROM     YourTable
    GROUP BY worklevel, umobile

    You can see an interactive example of this here. However, it seems more like you need to come with with a justification to determine _which_ value to use if duplicates exist or if they should exist at all (e.g. implement a unique constraint on the field).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 17, 2020 8:21 PM

All replies

  • User753101303 posted

    Hi,

    You could use https://www.w3schools.com/sql/sql_distinct.asp but it seems to me having the same worklevel with a distinct umobile might be possible ? If not I would rather add a unique constraints for example...

    I really dislike to see someone that uses DISTINCT to remove duplicates if the source query or database is not supposed anyway to allow those duplicates...

    Monday, February 17, 2020 6:29 PM
  • User836525179 posted

    thank you for reply but your seems is right

    having the same worklevel with a distinct umobile 

    it is like this

    1       a         b

    2       c         d

    3       a         b

    4       e         f

    i want to remove dublicated row with field "a" and just show one of them

    Monday, February 17, 2020 6:54 PM
  • User281315223 posted

    i want to remove dublicated row with field "a" and just show one of them

    How do you want to determine _which_ one to show? The DISTINCT keyword may help you with removing one of these, but more likely than not, if they exist, you might consider grouping the results by the value you are experiencing a duplicate of and simply select the first one that occurs:

    SELECT   MIN(id),
             worklevel,
             umobile
    FROM     YourTable
    GROUP BY worklevel, umobile

    You can see an interactive example of this here. However, it seems more like you need to come with with a justification to determine _which_ value to use if duplicates exist or if they should exist at all (e.g. implement a unique constraint on the field).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 17, 2020 8:21 PM