Skip to content

Instantly share code, notes, and snippets.

@Miista

Miista/SelectMaxDatetime.sql

Last active November 11, 2025 09:08
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Miista/43ecc7fc746951881c3f483593db1345 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Miista/43ecc7fc746951881c3f483593db1345 to your computer and use it in GitHub Desktop.
Download ZIP
Selecting the newest date in SQL
Raw
SelectMaxDatetime.sql
DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1
(
-- Other columns
[Id] INT PRIMARY KEY,
[Modified] DATETIME2
)
CREATE TABLE Table2
(
-- Other columns
[Id] INT PRIMARY KEY,
[Modified] DATETIME2,
[ForeignId] INT FOREIGN KEY REFERENCES Table1([Id])
)
INSERT INTO Table1 ([Id], [Modified])
VALUES (1, '2024-01-01 10:00:00'),
(2, '2024-02-01 10:00:00')
INSERT INTO Table2 ([Id], [Modified], [ForeignId])
VALUES (1, '2025-01-01 10:00:00', 1),
(2, '2025-02-01 10:00:00', 1),
(3, '2025-12-01 10:00:00', 2)
-- Approach 1: Using a correlated subquery
SELECT t1.[Id],
GREATEST(t1.[Modified], (SELECT MAX(t2.[Modified])
FROM Table2 AS t2
WHERE t2.ForeignId = t1.[Id]))
FROM Table1 AS t1
-- Approach 2: Using JOIN and GROUP BY
SELECT t1.[Id],
GREATEST(t1.[Modified], (MAX(t2.[Modified])))
FROM Table1 AS t1
JOIN Table2 t2 ON t2.ForeignId = t1.[Id]
GROUP BY t1.[Id], t1.[Modified]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment