How do I write postgres conditional SELECT query?

Question

I have a table that has 3 columns.

id | name | score | approve
--------------------
1  | foo  |  90   |   f
2  | foo  |  80   |   t

I want to

SELECT id WHERE name='foo'

with these conditions:

if approve is True, then return that one (only one will be true for the same name)
otherwise select the one that has highest score

I was looking into IF...ELSE but cannot even come up with a query that executes (despite a working one...)

How to set up the query command for this type of queries?

@RadimBača good question. In this case we can choose just one of them. Doesn't have to be random so it can always return the same person. We don't expect this to happen though as the score is actually going to 8 digits after decimal instead of an int — JChao
– JChao, Commented Oct 17, 2018 at 20:25

klin · Accepted Answer · 2018-10-17 20:21:07Z

1

In SQL, you can often use some logic by defining the right order and limit:

select id
from my_table
where name = 'foo'
order by approve desc, score desc
limit 1

answered Oct 17, 2018 at 20:21

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1