is there a way to not display rows with duplicate ID in SQL?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

I don't know if your SQL flavor (SQL Server) accepts this syntax, but in any case I would suggest just writing your query as a join:

SELECT o1.*
FROM O_SMFU o1
INNER JOIN
(
    SELECT ID, MAX(SMFU_nNEWNAIYO) AS MAX_SMFU_nNEWNAIYO
    FROM O_SMFU
    GROUP BY ID 
) o2
    ON o2.ID = o1.ID AND
       o2.MAX_SMFU_nNEWNAIYO = o1.SMFU_nNEWNAIYO;

Another way to write your query on SQL Server:

SELECT TOP 1 WITH TIES *
FROM O_SMFU
ORDER BY RANK() OVER (PARTITION BY ID ORDER BY SMFU_nNEWNAIYO DESC);