We help IT Professionals succeed at work.

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

Steve Olsen
Steve Olsen asked
on
90 Views
Last Modified: 2019-02-01
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.

Data.jpg
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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT

Commented:
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.
D BDatabase Developer

Commented:
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.
CERTIFIED EXPERT

Commented:
No, this cannot provide correct results because the MAX(CONSECUTIVE_NUMBER) must be related to the given MIN(VALID_TO_DATE) in a group.
D BDatabase Developer

Commented:
the group is EQUIPMENT, PLANT.
CERTIFIED EXPERT

Commented:
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.
D BDatabase Developer

Commented:
Duhhh! I see it now. Brain freeze. The wind chill is about 2° right now. :-)

Author

Commented:
Thank you!  The solution works great!

Steve