SQL Server, Extract the row values of which another field has maximum value

Hi Guys!

One of the typical requests that who writes in SQL is faced with is extract the row values of which another field has maximum value.

While extracting the maximum from a column is simple, this general case is less so.

The important thing is to recognize these cases.
Today we will see how to solve them.

Extract the row values of which another field has maximum value

Suppose having a table inventory_movements that contain a list of movements.

The fields in this table are: the product, the entry date of the movement and the value of the product on the date of entry of the movement.

This below is the table structure

Create Table inventory_movement
(id int identity(1,1) primary key,
Product varchar(40),
Datemov datetime,
ValueMov float
)
      

To better understand the concept let’s do an example

I insert some data into the table:

Insert into inventory_movements (Product, ValueMov, Datemov) values
('ACME Bird Seeds v1', 1.21, '08/06/2022'),
('ACME Bird Seeds v1', 1.23, '09/06/2022'),
('ACME Bird Seeds v1', 1.27, '10/06/2022'),
('Rocket-Powered Roller Skates', 1.11, '08/06/2022'),
('Rocket-Powered Roller Skates', 1.09, '10/06/2022'),
('Dehydrated Boulders', 1.23, '09/06/2022'),
('Dehydrated Boulders', 1.27, '10/06/2022')
      

This is the list of movements:

And this is what we want to achieve:

For each product (yes I am sponsored by Acme corporation) I need the most recent one (with the greatest date) and its value (field ValueMov)

 

Note that having to extract a field in addition to the Datemov field means that doing a MAX function is not enough.

We need to go to on the table inventory_movements twice.

Since we need to do on the table twice we can use a derived table (grouped).

The table grouped extract for each product the maximum value of the Datemov field.

Then we can join the main table inventory_movements with the table grouped using the product field and the datemov field.

This is the SQL command produced:

SELECT tt.valuemov, tt.*
FROM inventory_movements tt
INNER JOIN
    (SELECT Product, MAX(Datemov) AS MaxDateTime
    FROM inventory_movements
    GROUP BY Product) grouped
ON tt.Product = grouped.Product
WHERE tt.Datemov = grouped.MaxDateTime
      

But we can write the sam query using a CROSS APPLY operator

SELECT tt.ValueMov
FROM inventory_movements tt
CROSS APPLY
    (SELECT Product, MAX(Datemov) AS MaxDateTime
    FROM inventory_movements
       WHERE tt.Product = inventory_movements.Product
    GROUP BY Product) groupedtt
WHERE tt.Datemov = groupedtt.MaxDateTime
      

That's all for today!

Luke

 

Follow this blog if you want to learn to write performing Query or help me to support this blog by click the button  . Donors will be mentioned in the next posts.

I hope you appreciate that this month the blog is ad-free and therefore you can read it much better!

 

 

 

 

 

 

Previous post: BREAKING NEWS! SQL Server 2022 public preview for LINUX is now available! Let's install it step by step!