Using the smaller of two values in SQL condition

Question

I have a database of videos with a field for both the video width, and height.

I have queries set up to get videos of a specific resolution however it fails to return any videos that are portrait/vertical.

I would like to be able to do something like WHERE MIN(width, height) == 1080 but to my knowledge, this isn't possible.

Is there anyway I can get my desired effect in SQLite?

Does this answer your question? [Getting the minimum of two values in SQL](https://stackoverflow.com/questions/1947753/getting-the-minimum-of-two-values-in-sql) — Eldar, Jan 06 '21 at 06:58
@Eldar I don't know T-SQL and I'm not sure if it's applicable to SQLite. — Spedwards, Jan 06 '21 at 07:01
@juergend Because that would match videos at higher resolutions... 1080 was just an example, not the largest nor smallest. — Spedwards, Jan 06 '21 at 07:04

score 3 · Accepted Answer · answered Jan 06 '21 at 07:17

SQLite supports multi argument min function which behaves like LEAST function.

min(X,Y,...)

The multi-argument min() function returns the argument with the minimum value. The multi-argument min() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If none of the arguments to min() define a collating function, then the BINARY collating function is used. Note that min() is a simple function when it has 2 or more arguments but operates as an aggregate function if given only a single argument.

So you must be able to use it in the WHERE clause as you have mentioned in the question

Oh you're right. I tested this before I posted my question but I guess I made an error in the query as it had failed on me so I assumed it didn't work. Well now I feel stupid. Thank you very much for pointing that out for me. — Spedwards, Jan 06 '21 at 07:23

DhruvJoshi · Answer 2 · 2021-01-06T07:16:51.277

0

You are looking for a CASE expression in your SELECT.

Something like

CASE WHEN width>height THEN height ELSE width END = 1000

edited Jan 06 '21 at 07:16

answered Jan 06 '21 at 07:03

DhruvJoshi

17,041
6
41
60

1

Works a treat but was just pointed out that `MIN()` actually does work in this case which is simpler to read, write, and understand. Thank you for the answer though! – Spedwards Jan 06 '21 at 07:24

Using the smaller of two values in SQL condition

2 Answers2