CODEWITHSUNDEEP
postgresql
Mihai Niculita
Mihai Niculita

Reputation: 59

postgresql compute min value of columns conditiong on a value of other columns

can I do this with the standard SQL or I need to create a function for the following problem?

I have 14 columns, which represent 2 properties of 7 consecutive objects (the order from 1 to 7 is important), so table.object1prop1, ...,table.object1prop7,table.objects2prop2, ..., table.objects2prop7.

I need compute the minimum value of the property 2 of the 7 objects that have smaller values than a specific threshold for property 1. The values of the property 1 of the 7 objects take values on a ascending arithmetic scale. So property 1 of the object 1 will ever be smaller than property 2 of the objects 1.

Thanks in advance for any clue!

Upvotes: 1

Views: 157

Answers (1)

kgrittn
kgrittn

Reputation: 19471

This would be easier if the data were normalized. (Hint, any time you find a column name with a number in it, you are looking at a big red flag that the schema is not in 3rd normal form.) With the table as you describe, it will take a fair amount of code, but the greatest() and least() functions might be your best friends.

http://www.postgresql.org/docs/current/interactive/functions-conditional.html#FUNCTIONS-GREATEST-LEAST

If I had to write code for this, I would probably feed the values into a CTE and work from there.

Upvotes: 1

Related Questions