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

Time:2022-8-18

original data and target data

Implement SQL statement (max)


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

Implement the SQL statement (minimum)


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

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

Sometimes we need to get a maximum value from multiple columns in a table (the data types of these columns are the same, and each row record needs to get a maximum value). Three methods are described below, let's take a look.

First, we create a table and insert the required records 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 recorded in each row 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 three (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 find the maximum value later, which is very clever;

The second method uses the UNPIVOT keyword, which is often used in row to column, to convert and 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 find the maximum value.

The above is a personal experience, I hope it can give you a reference, and I hope you can support developpaer a lot. If there are any mistakes or not considered completely, please let me know.

Recommended Today

RabbitMQ study notes

Table of contents Related concepts RabbitMQ Install RabbitMQ core part Hello World Work Queues release confirmation switch fanout exchange (pub/sub) direct exchange topic exchange dead letter queue delay queue Release Confirmation Advanced The switch receives the message confirmation callback Fallback message to producer when switch is not routable Backup switch other idempotency priority queue lazy […]