CODEWITHSUNDEEP
mysqlpostgresql
Ramesh Somalagari
Ramesh Somalagari

Reputation: 535

How to find min max values from same row in postgresql

How to get the min,max values from row level(Not in columns) in PostgreSQL.

 postgresql> SELECT * FROM employee;
    +------+------+------------+--------------------+
    | id   | name | q1  |  q2 | q3  | q4 |
    +------+------+------------+--------------------+
    |    1 | John | 20  | 30  | 10  |  4  |
    |    2 | Ram  | 07  | 10  | 03  | 04  |
    |    3 | John | 05  | 03  | 15  | 40  | 
    |    4 | Sree | 12  | 05  | 20  | 25  |

I need single select query to get the min and max values. How to solve this. Where conditions is (Where id = 4) but I need the result is min value is 05 and max value is 25 from employee table.

Upvotes: 1

Views: 1999

Answers (2)

AdamMc331
AdamMc331

Reputation: 16691

I would recommend using the LEAST and GREATEST functions. Here's a reference.

The query you want can just select the id, and plug in each column into both functions like this:

SELECT id, name,
  LEAST(q1, q2, q3, q4) AS minValue,
  GREATEST(q1, q2, q3, q4) AS maxValue
FROM employee
WHERE id = 4;

Here is the SQL Fiddle.

EDIT

I tried the above using a PostgreSQL fiddle also, and both worked.

Upvotes: 6

SMA
SMA

Reputation: 37023

Use Formula for two columns a, b like:

for MAX - ((a+b) + abs(a-b))/2
for MIN - ((a+b) - abs(a-b))/2

So SQL will be like:

SELECT ((q1+q2) + ABS(q1-q2))/2 AS MAX, ((q1+q2) + ABS(q1-q2))/2 AS MIN
FROM MyTable

Generalize it in your case for 4 values.

Upvotes: 0

Related Questions