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
– Evan Volgas, Commented Dec 18, 2014 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. stackoverflow.com/questions/17361150/sql-unpivot-table — xQbert
– xQbert, Commented Dec 18, 2014 at 15:41
The question was edited with the postgres prompt @a_horse_with_no_name — Evan Volgas
– Evan Volgas, Commented Dec 19, 2014 at 14:37

AdamMc331 · Accepted Answer · 2014-12-18 18:22:14Z

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, 2014 at 18:22

AdamMc331

16.7k11 gold badges78 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ramesh Somalagari Over a year ago

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 ?

AdamMc331 Over a year ago

@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. Feel free to check that for updates.

SMA · Accepted Answer · 2014-12-18 15:45:43Z

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, 2014 at 15:45

SMA

37.2k8 gold badges53 silver badges76 bronze badges

Collectives™ on Stack Overflow

How to find min max values from same row in postgresql

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related