How to get the maximum or minimum value of a row in sql

Original data and target data

Implement SQL statements (maximum)

select
shop,
month,
greatest(dz,fz,sp) as max
from
tablename;

Implement SQL statements (minimum)

select
shop,
month,
least (dz,fz,sp) as min
from
tablename;

Supplement: Three methods to get the maximum value of multiple columns in a row in SQL Server

Sometimes we need to get a maximum value from multiple columns in a table (these columns have the same data type, and a maximum value is required for each row of records). There are three methods described below. Let’s take a look at them together.

First we create a table and insert the records we need into the table, as follows:

create table Demo(
 Guid varchar(50) not null default newid() primary key,
 Date1 datetime null,
 Date2 datetime null,
 Date3 datetime null
)

Then we insert test data into the table

insert into Demo(Date1,Date2,Date3) values
('2016-9-5','2016-8-6','2016-10-9'),
('2015-5-6','2015-8-6','2015-6-3'),
('2016-10-6','2015-6-6','2016-9-6')

select * from Demo

We need to get the maximum value of the three date columns for each row recorded above.

Method 1 (high efficiency):

select Guid,(select Max(NewDate) from (values ​​(Date1),(Date2),(Date3)) as #temp(NewDate)) as MaxDate from Demo

Method 2 (high efficiency):

select Guid, max(NewDate) as MaxDate from Demo unpivot (NewDate for DateVal in (Date1,Date2,Date3)) as u group by Guid

Method 3 (low efficiency, not recommended):

select Guid, (select max(NewDate) as MaxDate from (select Demo.Date1 as NewDate union select Demo.Date2 union select Demo.Date3)ud) MaxDate from Demo

The first method uses the values ​​clause to construct each row of data into a table with only one field, and then finds the maximum value, which is very clever;

The second method uses the UNPIVOT keyword, which is often used for row-to-column conversion, to convert and then display;

The third method is similar to the first method, but uses union to combine the three UpdateByAppDate fields into a result set with only one field and then finds the maximum value.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.