Why does SQL not have a straightforward way of querying using aggregate functions?

Question

It is an incredibly common scenario where I want to select the row (or rows) that have either the maximum or minimum value for some column - often a datetime stamp of some kind. It would seem logical that a simple way to do this would be something like this:

SELECT *
FROM MyTable
WHERE DateColumn = MAX(DateColumn)

This, of course, is not allowed. Aggregate functions are not allowed in a WHERE clause (though I don't know why, exactly). One could use a HAVING clause, but this doesn't actually work either:

SELECT *
FROM MyTable
HAVING DateColumn = MAX(DateColumn)

Instead, the only solutions seem to be some variation of a subquery, something like this:

SELECT *
FROM MyTable
WHERE DateColumn = (
  SELECT MAX(DateColumn)
  FROM MyTable
)

Why is such a common need made so complicated? The intent of both of my examples above seems quite obvious, so why can't the SQL compiler be made to understand them? Or, if there is some technical reason why the existing implementation of WHERE cannot handle this syntax, why has no simple syntax been added to the language? I run into this particular need very frequently, and I see from searching online that I am hardly the only one. It would seem like the language should have accounted for this LONG ago, but it never has. Is there some serious technical or logical limitation I am missing here that makes this unrealistic?

Answer 1

It's SQL. It's set oriented. And it's only ordered if you deliberately state that - and how - you want your data ordered.

And there is, in every DBMS I know, a means to limit the number of rows returned.

In SQL Server, your query would need to be:

SELECT TOP(1)
  *
FROM mytable
ORDER BY datecolumn DESC;

And just because we can: let's assume you want the newest row for each ID. There, decent DBMS-s have OLAP functions like ROWNUM() OVER() to help you with that task.

Input would be a table with a bunch of identifiers and several different dates per identifier. And you want the newest for each identifier. See here: ...

WITH
indata(id,dt) AS (
            SELECT 1, DATE '2022-01-01'
  UNION ALL SELECT 1, DATE '2022-01-02'
  UNION ALL SELECT 2, DATE '2022-01-01'
  UNION ALL SELECT 2, DATE '2022-01-02'
  UNION ALL SELECT 3, DATE '2022-01-01'
  UNION ALL SELECT 3, DATE '2022-01-02'
  UNION ALL SELECT 4, DATE '2022-01-01'
  UNION ALL SELECT 4, DATE '2022-01-02'
)
,
w_rownum AS (
  SELECT
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY dt DESC) AS rn
  , * 
  FROM indata
)
SELECT
  id  
, dt
FROM w_rownum
WHERE rn=1
ORDER BY id; 
-- out  id |     dt     
-- out ----+------------
-- out   1 | 2022-01-02
-- out   2 | 2022-01-02
-- out   3 | 2022-01-02
-- out   4 | 2022-01-02