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

Steve Olsen
Steve Olsen used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
pcelba

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

Commented:
Test with data: 
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 Q1.EQUIPMENT, Q1.PLANT, Q1.VALID_TO_DATE, MAX(Q1.CONSECUTIVE_NUMBER) "CONSECUTIVE_NUMBER"
  FROM @t Q1
 INNER JOIN (SELECT EQUIPMENT, MIN(VALID_TO_DATE) "VALID_TO_DATE"
               FROM @t 
              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

SharathData Engineer

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

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Read More
pcelba

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.
Doug BishopDatabase 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.
pcelba

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.
Doug BishopDatabase Developer

Commented:
the group is EQUIPMENT, PLANT.
pcelba

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.
Doug BishopDatabase Developer

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

Author

Commented:
Thank you!  The solution works great!

Steve

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial