Return Only Max Value With Distinct-CodePudding

I've below data in database table as follows:

Table Data:

1002    1
1002    0

So expected output are these:

Condition 1:

1002    1
1002    0

Expected Output:

1002    1

Condition 2:

1002    1
1002    1

Expected Output:

1002    1

Condition 3:

1002    0
1002    0

Expected Output:

1002    0

So tried it with the following that returns only 1 every time:

SELECT DISTINCT M.ID, (SELECT MAX(K.VAL) FROM Table k WHERE K.ID = M.ID) VAL FROM Table m WHERE M.VAL = '1002';

Doing the join in the same table but didn't get much help. Any simple suggestion with one liner query would be appreciated.

CodePudding user response：

Use MAX and GROUP BY:

SELECT id,
       MAX(val) AS val
FROM   table_name
WHERE  id = 1002
GROUP BY id

db<>fiddle here

CodePudding user response：

You can also sort the column you want max to descend and extract the first record

SELECT id,val
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY val desc) AS seq,
    id,val
    FROM table_name
)tb
WHERE seq = 1;