3 ms·
Given: create table conversations ( message_id integer primary key, conversation_id integer, timestamp timestamp, user_id integer,
by twic 8y ago
Given:
create table conversations (
message_id integer primary key,
conversation_id integer,
timestamp timestamp,
user_id integer,
message varchar
);
Then:
select distinct on (conversation_id) *
from conversations
order by conversation_id, timestamp desc;
Should do it. Although i believe "select distinct on" is a PostgreSQL extension rather than standard SQL.
Now, what i don't know is how this is planned for a large table, or what indices would let you get a sensible plan. Maybe just an index on (conversation_id, timestamp)?
- tfehring 8y agoExtension 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.