Cacher is the code snippet organizer for pro developers

/* SQL MAX() on date value with subquery
To get data of 'agent_code', 'ord_date' and 'cust_code' from the 'orders' table with the following conditions -

'ord_date' will be equal to the maximum 'ord_date' of 'orders' table with following condition -

  'agent_code' of 'orders' table must be equal to the 'agent code' of
  'orders' table mentioned as alias 'S'

the following SQL statement can be used :
*/
SELECT agent_code, ord_date, cust_code 
FROM orders S 
WHERE ord_date=(
SELECT MAX(ord_date) 
FROM orders 
WHERE agent_code = S.agent_code);

/* Example */

SELECT 
 [Roster Code]
      ,[Start Date]
      ,[Page]
      ,[Cycle Length]
      ,[Date]
      ,[Day Of Week]
      ,[Hours]
      ,[Rate]
      ,[Allowance]
      ,[Shift]
      ,[Time Start]
      ,[Time End]
 FROM Rostered r
WHERE r.[Roster Code] = '15/13 WQLD A' AND  r.[DATE] = '2020-06-27'  AND r.[Start Date] = (
	SELECT MAX([Start Date]) 
FROM Rostered 
WHERE [Roster Code] = r.[Roster Code] AND [Start Date] < '2020-06-27'

)