DB query with join?

Question

SQL

There are two
urls tables: id, name, created_at, updated_at;
and
urls_checks: id, url_id, status, created_at, updated_at;
I make a request

SELECT * FROM urls JOIN url_checks ON urls.id = url_checks.url_id

because there can be many identical url_id in url_checks I need only 1 maximum for the url_checks.updated_at field I
don't understand how to filter it? tried to add a type
HAVING/WHERE max(url_checks.updated_at)
does not work .... neither HAVING nor WHERE, tell me where to get information, if such a request is possible of course? maybe I messed up with the syntax ...
I'm trying to google, but I can't find my case.

Answer 1

We use window functions:

SELECT 
  *
FROM (
  SELECT
    url_id,
    status,
    updated_at,
    ROW_NUMBER() OVER (PARTITION BY url_id ORDER BY updated_at DESC) rn
  FROM urls_checks
  ORDER BY urls_checks DESC
) checks 
JOIN urls on urls.id = checks.url_id
WHERE rn = 1;

SQL fiddle