How to find min max values from same row in postgresql

Question

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.

You are asking about PostgreSQL, using a MySQL prompt, and tagged your question with both MySQL and PostgreSQL. Can you please clarify which one applies here? — Evan Volgas, Dec 18 '14 at 15:40
Concept: 1st unpivot the data using whatever mechanism applies to the DBengine in question. Next, simply run a min max function on the unpivoted results. http://stackoverflow.com/questions/17361150/sql-unpivot-table — xQbert, Dec 18 '14 at 15:41
The question was edited with the postgres prompt @a_horse_with_no_name — Evan Volgas, Dec 19 '14 at 14:37

score 6 · Answer 1 · answered Dec 18 '14 at 18:22

6

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.

answered Dec 18 '14 at 18:22

AdamMc331

16,492
10
71
133

Thanks Adam. I have got the Min and Max values in the row. If I have to get the Question Id also, how should I include that in the query ? – Ramesh Somalagari Dec 19 '14 at 06:25
@Ramesh I feel like that is a different question altogether. You never mentioned that in your current question. And, to be honest, I couldn't figure it out. So I was proactive and I asked a new [question](http://stackoverflow.com/questions/27567600/how-to-get-the-column-name-of-the-result-of-a-least-function). Feel free to check that for updates. – AdamMc331 Dec 19 '14 at 13:59

score 0 · Answer 2 · answered Dec 18 '14 at 15:45

0

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.

answered Dec 18 '14 at 15:45

SMA

36,381
8
49
73

How to find min max values from same row in postgresql

2 Answers2