Search code examples
sqlduplicatesbooleansql-server-2014

Is there a way to not display rows with duplicate ID in SQL?

This is my query

   SELECT * 
FROM O_SMFU WHERE (ID , SMFU_nNEWNAIYO) IN 
( SELECT ID , MAX(SMFU_nNEWNAIYO)
  FROM O_SMFU
  GROUP BY ID 
)

When ID is duplicated show only the row with max SMFU_nNEWNAIYO

but it gives me an error :

Message 4145, Level 15, State 1, Line 49
A non-Boolean expression was specified near',' for a context that requires a condition.

data I have:

ID        SMFU_nNEWNAIYO
703            1930
703            1920
703            1910
705            1950
725            1900
726            1900
726            1900
886            1900
886            1900

data I want to get:

ID         SMFU_nNEWNAIYO
703            1930
705            1950
725            1900
726            1900
886            1900
Solution

  • try this query

    SELECT id,MAX(SMFU_nNEWNAIYO)
FROM O_SMFU
GROUP BY id

    This query will give you the data you want to get