Select latest model with unique column combination

[fpolli]

I have a database query issue that seems to me like it should be simple because it is a very normal need, but I cannot figure out a way to do it.

I have a table of transactions with a user, a seller, a category and other attributes.

What I need is the most recently updated transaction in each category for a seller-buyer combination.

Please help me. You are my only hope.

Thank you.

[victormoni]

Hello @fpolli 👋😄

Here's how you can get the latest transaction per unique combination of
user, seller, and category.

Using ROW_NUMBER (works in PostgreSQL, SQL Server, Oracle, etc.)

SELECT user_id,
       seller_id,
       category,
       transaction_id,
       updated_at
FROM (
    SELECT t.*,
           ROW_NUMBER() OVER (
               PARTITION BY user_id, seller_id, category
               ORDER BY updated_at DESC
           ) AS rn
    FROM transactions t
) sub
WHERE rn = 1;

This returns only the most recent transaction for each
(user, seller, category) combination.

Using DISTINCT ON (PostgreSQL only)

SELECT DISTINCT ON (user_id, seller_id, category)
       user_id,
       seller_id,
       category,
       transaction_id,
       updated_at
FROM transactions
ORDER BY user_id, seller_id, category, updated_at DESC;

References:

• PostgreSQL DISTINCT ON

• ROW_NUMBER() Window Function (SQL Server docs)

• Window Functions in SQL

I hope it helps 🙏

[fpolli]

Thanks! I'm going to try this now 🤓

…

On Sat, Aug 16, 2025 at 6:01 PM, Victor Moni ***@***.***(mailto:On Sat, Aug 16, 2025 at 6:01 PM, Victor Moni <<a href=)> wrote: Hello ***@***.***(https://github.com/fpolli) 👋😄 Here's how you can get the latest transaction per unique combination of user, seller, and category. Using ROW_NUMBER (works in PostgreSQL, SQL Server, Oracle, etc.) SELECT user_id, seller_id, category, transaction_id, updated_at FROM ( SELECT t. * , ROW_NUMBER() OVER ( PARTITION BY user_id, seller_id, category ORDER BY updated_at DESC ) AS rn FROM transactions t ) sub WHERE rn = 1 ; This returns only the most recent transaction for each (user, seller, category) combination. Using DISTINCT ON (PostgreSQL only) SELECT DISTINCT ON (user_id, seller_id, category) user_id, seller_id, category, transaction_id, updated_at FROM transactions ORDER BY user_id, seller_id, category, updated_at DESC ; References: - [PostgreSQL DISTINCT ON](https://www.postgresql.org/docs/current/queries-select-lists.html#QUERIES-DISTINCT)\ - [ROW_NUMBER() Window Function (SQL Server docs)](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql)\ - [Window Functions in SQL](https://mode.com/sql-tutorial/sql-window-functions/) I hope it helps 🙏 — Reply to this email directly, [view it on GitHub](#56605 (comment)), or [unsubscribe](https://github.com/notifications/unsubscribe-auth/ABJXG7TNKXDP2NJWDIQ2Q3T3N7H4ZAVCNFSM6AAAAACDRGJCZSVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTIMJSHE2DONY). You are receiving this because you were mentioned.Message ID: ***@***.***>