CODEWITHSUNDEEP
mysqlsql
Engl12
Engl12

Reputation: 139

Select min and max for each row with multiple columns

I'm trying to run a query to select the min and max for each row, with multiple columns.

Here is some data that I'm trying to select from - 

ITEM NO IDN IDB     IDC     IDE    IDF  IDG     IDH
A33     1.5  1.25    5      14.5   1.89 1.254   1.21
A34     1.6  1.257   5.95   52.1   1.2  1.256   1.235

The end result I need is - 

ITEM NO     MIN   MAX
A33         1.2   14.5
A34         1.2   52.1

Is there any simple way of doing this in a sql query?

Upvotes: 0

Views: 1515

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Assuming you have no NULL values (as in the sample data), use least() and greatest():

select item_no, least(idn, idb, idc, ide, idf, idg, idh) as `min`, 
       greatest(idn, idb, idc, ide, idf, idg, idh) as `max`
from table t;

EDIT:

With NULL values, this becomes much harder. One way is if you can define a valid range, then something like this works:

select item_no,
       least(coalesce(idn, 999999999), coalesce(idb, 999999999), coalesce(idc, 999999999), coalesce(ide, 999999999), 
             coalesce(idf, 999999999), coalesce(idg, 999999999), coalesce(idh, 999999999)
            ) as `min`, 
       greatest(coalesce(idn, -1), coalesce(idb, -1), coalesce(idc, -1), coalesce(ide, -1), 
                coalesce(idf, -1), coalesce(idg, -1), coalesce(idh, -1)
               ) as `max`
from table t;

You can convert back to NULL with case logic or nullif(), if all the values in a row can be NULL:

select item_no,
       nullif(least(coalesce(idn, 999999999), coalesce(idb, 999999999), coalesce(idc, 999999999), coalesce(ide, 999999999), 
                    coalesce(idf, 999999999), coalesce(idg, 999999999), coalesce(idh, 999999999)
                   ),
              999999999) as `min`, 
       nullif(greatest(coalesce(idn, -1), coalesce(idb, -1), coalesce(idc, -1), coalesce(ide, -1), 
                       coalesce(idf, -1), coalesce(idg, -1), coalesce(idh, -1)
                      ),
              -1) as `max`
from table t;

Upvotes: 6

Related Questions