Link to home
Start Free TrialLog in
Avatar of Steve Olsen
Steve Olsen

asked on

SQL Statement - Min value in first column, Max value in related second column

I'm trying to create a SQL statement that will select the minimum date value in a column called "Valid_To_Date" and then the Max value in the Consecutive_Number column so I end up selecting the values I've highlighted in yellow in the data sample picture I've attached.  I want to limit the MAX Consecutive_Number to only those rows where there is a MIN Valid_To_Date value.

User generated image
Below is my attempt at writing the sql statement.  I can correctly select the MIN date value but when it selects the MAX Consecutive_Number value it comes up with the value of '007' when I would like it to come up with '002' by limiting the values it considers for 'MAX' to just those rows that hold a MIN value for Valid_To_Date.

SELECT Q1.EQUIPMENT, Q1.PLANT, Q1.VALID_TO_DATE, Q1.CONSECUTIVE_NUMBER
FROM MyDatabase.EQUIPMENT_LOCATION Q1
INNER JOIN
(SELECT EQUIPMENT, MIN(VALID_TO_DATE) "VALID_TO_DATE", MAX(CONSECUTIVE_NUMBER) "CONSECUTIVE_NUMBER"  
FROM MyDatabase.EQUIPMENT_LOCATION 
GROUP BY 1) AS Q2
ON Q1.EQUIPMENT = Q2.EQUIPMENT
AND Q1.VALID_TO_DATE = Q2.VALID_TO_DATE
AND Q1.CONSECUTIVE_NUMBER = Q2.CONSECUTIVE_NUMBER

Open in new window

Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Hopefully the PLANT can contain more EQUIPMENTs and one EQUIPMENT cannot occur in more PLANTs:
SELECT Q1.EQUIPMENT, Q1.PLANT, Q1.VALID_TO_DATE, MAX(Q1.CONSECUTIVE_NUMBER) "CONSECUTIVE_NUMBER"
  FROM MyDatabase.EQUIPMENT_LOCATION Q1
 INNER JOIN (SELECT EQUIPMENT, MIN(VALID_TO_DATE) "VALID_TO_DATE"
               FROM MyDatabase.EQUIPMENT_LOCATION 
              GROUP BY EQUIPMENT) AS Q2
         ON Q2.EQUIPMENT = Q1.EQUIPMENT AND Q2.VALID_TO_DATE = Q1.VALID_TO_DATE
 GROUP BY Q1.EQUIPMENT, Q1.PLANT, Q1.VALID_TO_DATE

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can window function ROW_NUMBER if your database supports.

DECLARE @t TABLE
  (EQUIPMENT int, PLANT char(3), VALID_TO_DATE date, CONSECUTIVE_NUMBER char(3))
INSERT INTO @t VALUES 
   (123, 'ABC', '12/31/9999', '007')
  ,(123, 'ABC', '7/24/2018', '001')
  ,(123, 'ABC', '7/24/2018', '002')
  
 SELECT *
   FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY VALID_TO_DATE, CONSECUTIVE_NUMBER DESC) rn 
           FROM @t) t1
  WHERE rn = 1

Open in new window

The above code does not work for more EQUIPMENTs. ROW_NUMBER needs to be partitioned:
DECLARE @t TABLE
  (EQUIPMENT int, PLANT char(3), VALID_TO_DATE date, CONSECUTIVE_NUMBER char(3))
INSERT INTO @t VALUES 
   (123, 'ABC', '12/31/9999', '007')
  ,(123, 'ABC', '7/24/2018', '001')
  ,(123, 'ABC', '7/24/2018', '002')
  ,(124, 'ABC', '12/31/9999', '002')

 SELECT *
   FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY VALID_TO_DATE, CONSECUTIVE_NUMBER DESC) rn 
           FROM @t) t1
  WHERE rn = 1
  
 SELECT *
   FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY EQUIPMENT ORDER BY VALID_TO_DATE, CONSECUTIVE_NUMBER DESC) rn 
           FROM @t) t1
  WHERE rn = 1

Open in new window

Of course, depending on the EQUIPMENT relation to PLANT it could also need partitioning by PLANT but we don't have sufficient info from the question author.
Why not just:
SELECT EQUIPMENT,
       PLANT,
       MIN(VALID_TO_DATE) AS VALID_TO_DATE,
       MAX(CONSECUTIVE_NUMBER) CONSECUTIVE_NUMBER
FROM @t
GROUP BY EQUIPMENT,
         PLANT;

Open in new window

This is assuming you also want grouping by PLANT. If not then @pcelba's solution is best. No need for JOINs.
No, this cannot provide correct results because the MAX(CONSECUTIVE_NUMBER) must be related to the given MIN(VALID_TO_DATE) in a group.
the group is EQUIPMENT, PLANT.
No matter what is the group here. Either EQUIPMENT or EQUIPMENT+PLANT provides incorrect results. The MAX() is not related to this group but to the MIN(VALID_TO_DATE) in this group.
Duhhh! I see it now. Brain freeze. The wind chill is about 2° right now. :-)
Avatar of Steve Olsen
Steve Olsen

ASKER

Thank you!  The solution works great!

Steve