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.
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_NUMBERFROM MyDatabase.EQUIPMENT_LOCATION Q1INNER JOIN(SELECT EQUIPMENT, MIN(VALID_TO_DATE) "VALID_TO_DATE", MAX(CONSECUTIVE_NUMBER) "CONSECUTIVE_NUMBER" FROM MyDatabase.EQUIPMENT_LOCATION GROUP BY 1) AS Q2ON Q1.EQUIPMENT = Q2.EQUIPMENTAND Q1.VALID_TO_DATE = Q2.VALID_TO_DATEAND Q1.CONSECUTIVE_NUMBER = Q2.CONSECUTIVE_NUMBER
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
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.
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 B
Duhhh! I see it now. Brain freeze. The wind chill is about 2° right now. :-)
Open in new window