How to join a table with a row in another table having the highest id?

Question

I have two Postgres tables: 'user' with unique rows, and 'address' where a single user can have multiple addresses. I want to join specific 'user' row with one row from 'address' where the address.id is the highest max(id).

I wrote the following query which works OK but I suspect that when there are many addresses for one user, this query will produce a really large intermediate aggregate before it returns the the final results. Is there a better way to write this query?

select "user".id, 
       "user".username,     
       "user".trader_id, 
       address.id,                                             
       address.street, 
       address.trader_id 
    from "user", address 
  where "user".id = 6 
      and  address.trader_id = "user".trader_id                         
 order by address.id desc
 limit 1;

Answer 1

Your query is pretty good already for getting a single user. Definitely much faster than running a window function over the whole address table in a subquery.
Your query, only slightly simplified with a USING clause:

SELECT trader_id, u.id AS user_id, u.username, a.id AS adr_id, a.street
FROM   "user"  u
JOIN   address a USING (trader_id)
WHERE  u.id = 6
ORDER  BY a.id DESC
LIMIT  1;

A multicolumn index on address(trader_id, id) will give you optimal performance. Plus, obviously, an index on "user"(id). (No "intermediate aggregate" like you apprehended with LIMIT 1!) See:

• Optimize GROUP BY query to retrieve latest row per user
• Select first row in each GROUP BY group?

Alternatively, the same technique in a LATERAL subquery. Works for retrieving one or more users:

SELECT u.trader_id, u.id AS user_id, u.username, a.*
FROM   "user"  u
LEFT   JOIN LATERAL (
   SELECT a.id AS adr_id, a.street
   FROM   address a
   WHERE  a.trader_id = u.trader_id
   ORDER  BY a.id DESC
   LIMIT  1
   ) a ON true
WHERE  u.id = 6;  -- or for more than just the one

About LATERAL subqueries:

• What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

Also using LEFT JOIN to preserve users without any address.

If you are going to use a window function, use row_number() rather than rank(). Do it in a LATERAL subquery while only retrieving a single (or few) user(s), to only involve relevant rows. And, unless you run Postgres 16 or later, add the frame clause ROWS UNBOUNDED PRECEDING for performance:

SELECT u.trader_id, u.id AS user_id, u.username, a.*
FROM   "user"  u
LEFT   JOIN LATERAL (
   SELECT id AS adr_id, street
        , row_number() OVER (ORDER BY id DESC ROWS UNBOUNDED PRECEDING) AS rn
   FROM   address a
   WHERE  a.trader_id = u.trader_id
   ) a ON a.rn = 1
WHERE  u.id = 6;  -- or for more than one user

Why ROWS UNBOUNDED PRECEDING? See:

• Find first 3 orders for each customer
• Window function in scalar subquery does not work as expect

Or use DISTINCT ON:

SELECT DISTINCT ON (traider_id)
       trader_id, u.id AS user_id, u.username, a.id AS adr_id, a.street
FROM   "user"  u
JOIN   address a USING (trader_id)  -- or LEFT JOIN?
WHERE  u.id = 6
ORDER  BY trader_id, a.id DESC;

See:

• Select first row in each GROUP BY group?

__

Aside: Rather don't use reserved words like "user" as identifier.