CODEWITHSUNDEEP
sqlpostgresql
JamesF
JamesF

Reputation: 449

What's an elegant way to find the minimum value in each row of a table?

I've got a table which has a row per product, and the price that product has on ten different merchants. What I'd like to see is the minimum price each product has among those different merchants.

In Excel this would be easy, because the MIN() function there works on any set of cells, whether they're arranged horizontally or vertically. However, MIN() in SQL only acts on columns, so I'd be able to find the cheapest price merchant 1 had across all products, etc.

Is there an elegant way to obtain the minimum price for each row? (Are there OLAP functions that would do this, or does the problem have to be approached using a loop?)

Upvotes: 1

Views: 184

Answers (1)

Blorgbeard
Blorgbeard

Reputation: 103467

In PostgreSQL, you can do:

select least(price1, price2, price3, ..)
from products

LEAST gives you the minimum value of a list of values. It's the non-aggregate version of MIN.

Upvotes: 6

Related Questions