CODEWITHSUNDEEP
sqlpostgresql
Margulan Zharkenov
Margulan Zharkenov

Reputation: 458

How to query based on priority of column values

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 |

Upvotes: 1

Views: 1180

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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;

Upvotes: 2

Margulan Zharkenov
Margulan Zharkenov

Reputation: 458

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;

Upvotes: 1

Jim Macaulay
Jim Macaulay

Reputation: 5141

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;

Upvotes: 1

Related Questions