SQL to join to the best matching row

Question

I have a wiki system where there is a central table, Article, that has many Revisions in their own table. The Revisions each contain a created_at time and date column.

I want to update the Articles to contain a denormalized field sort_name from the most recent Revision's name field.

What SQL command can I issue to fill in each Article's sort_name field with its most recent Revision's name field?

For what it's worth, I'm on PostgreSQL.

Answer 1

Not 100% sure the PostgreSQL syntax, so you might have to change this a little, but the idea is:

UPDATE Articles
SET sort_name = (SELECT FIRST(name)
                 FROM Revisions
                 WHERE Revisions.id = Articles.id
                 ORDER BY created_at DESC)

Answer 2

If I understand you correctly, you want to join to the most recent revision. Here is an optimized way to select the most recent revision to do the update. Let me know if this wasn't what you were looking for. It's written for T-SQL since I'm not familiar with PostgreSQL

UPDATE ARTICLES
SET SORT_NAME = lastHist.NAME
FROM ARTICLES AS t
    --Join the the most recent history entries
        INNER JOIN  REVISION lastHis ON t.ID = lastHis.FK_ID
        --limits to the last history in the WHERE statement
            LEFT JOIN REVISION his2 on lastHis.FK_ID = his2.FK_ID and lastHis.CREATED_TIME < his2.CREATED_TIME
WHERE his2.ID is null

Answer 3

Table structure would help define your problem better.

For Postgres:

UPDATE ARTICLES ar
SET sort_name = (SELECT name FROM Revisions rev 
                 WHERE rev.article_id = ar.article_id
                 ORDER BY rev.createdate DESC LIMIT 1)