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.

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

Steve OlsenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
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

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Start Today!
pcelbaCommented:
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 DeveloperCommented:
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.
pcelbaCommented:
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 DeveloperCommented:
the group is EQUIPMENT, PLANT.
pcelbaCommented:
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 DeveloperCommented:
Duhhh! I see it now. Brain freeze. The wind chill is about 2° right now. :-)
Steve OlsenAuthor Commented:
Thank you!  The solution works great!

Steve
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.