Comment by GordonS

SELECT DISTINCT ON (album_id) * FROM tracks GROUP BY album_id ORDER BY milliseconds DESC;

For those unfamiliar with Postgres, DISTINCT ON takes only one row for each of the groups based on the supplied columns. So in this case, it will return only one row per album_id.

Without an ORDER BY, DISTINCT ON chooses a random row (not actually, but you can’t rely on it.) Since we ORDER BY milliseconds DESC, the first row of each group will be the longest one.

It's a pretty common pattern (take the top N by group with some order), so I'd expect that it's a familiar pattern to a lot of people who regularly do analytical queries in SQL. It's clearer with formatting/naming:

    WITH ranked_tracks AS (
        SELECT *,
            ROW_NUMBER() OVER ( 
                PARTITION BY album_id
                ORDER BY milliseconds DESC
            ) AS row_num
        FROM tracks
    )
    SELECT *
    FROM ranked_tracks
    WHERE
        row_num = 1

But it is still super ugly for such a common need. If I were to add syntax to make this kind of thing easier, I'd just go for a syntax that made something like this valid:

    SELECT *
    FROM tracks
    WHERE
        ROW_NUMBER() OVER ( 
            PARTITION BY album_id
            ORDER BY milliseconds DESC
        ) = 1

Which apparently the QUALIFY statement does in a few dialects.

> making the investment is worthwhile and pays off in small and large ways

And... 'making the investment' takes time, and means that time is not able to be invested someplace else.

If the majority of your job is writing SQL or similar (data access, etc) then sure - yes, learn more of those tools. Some folks have a wider range of responsibilities that means you have to decide what to make more time investments in, and saying 'yes' to something is necessarily saying 'no' to other things.

The hard part of SQL is thinking relationally. This obviously doesn’t do anything to affect that — if they actually thought this, they’d be making the same mistake as SQL itself “the reason business users don’t program is because it’s not english enough”

The problem this is resolving, if it successfully resolves anything at all, is that the SQL language is a mess of random keywords, inconsistent syntax requirements and generates some of the worst error messages known to man. It’s an attempt at making SQL a consistent, simple language — ideally exposing the data model more directly and with less noise