CODEWITHSUNDEEP
sqldatabasesqlite
Spedwards
Spedwards

Reputation: 4492

Using the smaller of two values in SQL condition

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?

Upvotes: 0

Views: 3118

Answers (2)

Popeye
Popeye

Reputation: 35910

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

Upvotes: 4

DhruvJoshi
DhruvJoshi

Reputation: 17126

You are looking for a CASE expression in your SELECT.

Something like

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

Upvotes: 0

Related Questions