SQL: Select Rows with Max Value of One Colume For Each Value

Question

I have a table like this:

Id   Col1    Col2 ...

1    NULL    1000 
1    x       1001    
1    y       1002 
2    x       2000  
2    y       2001   
2    NULL    2002
3    z       3000
....

I want to select all rows with Id = 1 or 2 where:

1. Col1 is NULL

OR

2. Col1 is NOT NULL and Col2 is max among rows with same Id

Then the result should be

(1, NULL 1000)
(1, y 1002)
(2, y 2001)
(2, NULL 2002)

For the first condition I can think of following SQL statement:

SELECT * FROM my_table WHERE Id IN (1, 2) and action IS NULL

For the second condition, if I use something like this:

SELECT * FROM my_table WHERE Id IN (1, 2) and action IS NOY NULL ORDER BY Col2 LIMIT1

It will only give me one row with max Col2 among all results from Id=1 and Id=2.

How can I get every row with Max Col2 for each Id group? Got a feeling of using GROUP BY, but have no idea how to apply it. Please give me a hint! Thanks!

(It's basically the batch version of this question I posted earlier)

Answer 1

Hmmm . . . how about the two conditions in a where clause:

select t.*
from my_table t
where t.col1 is null or 
      t.col2 = (select max(t2.col2) from my_table t2 where t2.id = t.id and t2.col1 is not null);