I have a table
| group | col1 | col2 |
| 1 | test1 | val1 |
| 1 | test2 | val2 |
| 3 | test3 | val3 |
| 3 | test4 | val4 |
I need to select rows by priority. For example, if row has col1 value as test1 so show it. If it's not then show test2. Don't remember about group. Just if values in one group. I expect this result:
| group | col1 | col2 |
| 1 | test1 | val1 |
| 3 | test3 | val3 |
In standard SQL, you seem to want:
select t.*
from t
order by (case when col1 = 'test1' then 1
when col2 = 'test2' then 2
else 3
end)
fetch first 1 row only;
EDIT:
For the revised question, you can use distinct on:
select distinct on (group) t.*
from t
order by group,
(col1 = 'test1') desc,
(col1 = 'test2') desc;
Please use below query,
select * from
(select group, col1, col2, row_number() over (partition by group order by col1) as rnk
from table) where rnk = 1;
This is the query that work!
select * from
(select group,
col1,
col2,
row_number() over (partition by group order by (case when col1 = 'test1' then 2
when col1 = 'test2' then 1
else 3
end)) as rnk
from test) AS tab1 where rnk = 1;