troubleshooting Question

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

Avatar of Steve Olsen
Steve Olsen asked on
SQL
10 Comments1 Solution96 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
pcelba

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros