asked on
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.
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
SQL
Last Comment
Steve Olsen
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
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;
No, this cannot provide correct results because the MAX(CONSECUTIVE_NUMBER) must be related to the given MIN(VALID_TO_DATE) in a group.
the group is EQUIPMENT, PLANT.
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.
Duhhh! I see it now. Brain freeze. The wind chill is about 2° right now. :-)
ASKER
Thank you! The solution works great!
Steve
Steve
Open in new window