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
CERTIFIED EXPERT
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
Data Engineer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
Database Developer
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;
CERTIFIED EXPERT
No, this cannot provide correct results because the MAX(CONSECUTIVE_NUMBER) must be related to the given MIN(VALID_TO_DATE) in a group.