3 ms·
Extension or not, that's a really useful shortcut. Coming from SQL Server, the best option I know of requires a CTE or subquery: WITH CTE_messages AS (
by tfehring 8y ago
Extension or not, that's a really useful shortcut. Coming from SQL Server, the best option I know of requires a CTE or subquery:
WITH CTE_messages AS
(
SELECT
*
,row_nbr = ROW_NUMBER() OVER(PARTITION BY conversation_id
ORDER BY timestamp DESC)
FROM
conversations
)
SELECT
*
FROM
CTE_messages
WHERE row_nbr = 1
ORDER BY
conversation_id
This does give a performant plan in SQL Server with an index on (conversation_id, timestamp), for what it's worth. I imagine the same is true for this syntax in PostgreSQL. But SELECT DISTINCT sometimes does strange things to execution plans in general, and I wouldn't be surprised if that extends to SELECT DISTINCT ON.