score:0
You can achieve this using Window Functions
SELECT *
FROM
(select *, rank() OVER (PARTITION BY date, user_from,user_to ORDER BY time desc ) AS pos
FROM messages
) AS msg
WHERE pos = 1
I strongly advice you to note use PostgreSQL's tokens as column name!
score:1
Here is how I did it:
SELECT DISTINCT ON (user_from, user_to) *
FROM messages
ORDER BY user_from, user_to, date+time DESC
More questions
- Select distinct last messages by pairs in postgres
- Solution for speeding up a slow SELECT DISTINCT query in Postgres
- Postgres Rails Select Distinct with Order
- Slow select distinct query on postgres
- rails 4 select distinct on multiple columns in postgres with where and limit
- django + postgres and select distinct
- Node / Postgres SQL Select distinct entries then put all other entries with the same reference into one column
- Select Distinct (case insensitive) on Postgres
- postgres select count distinct returning unexpected extra row
- Rails SELECT Distinct with postgres
- Postgres select distinct based on timestamp
- Slow Postgres Query with a SELECT DISTINCT and a Join
- select last n lines of a text field in postgres on the bases of a split character
- Postgres select distinct values in OVER() function
- Rails PostgreSQL select all messages in a ticket that last message was by admin
- Select last ordered value for each distinct key in a table
- Postgres - how select jsonb key value pairs as colums?
- select last of an item for each user in postgres
- Postgres select distinct of cartesian product
- select distinct data from postgres table
- Postgres Query: Select the row with maximum value on a column from two distinct rows
- Select Last Rows with Distinct Field
- Postgres How to select parent table based on child's last records?
- postgres : get the last two messages to a member from conversation
- Count unread messages in chats after last read message id in Postgres
- Select chat messages unread by current user id in Postgres
- Postgres “missing FROM-clause entry” error on distinct select query
- Postgres select distinct with different order by without using a subquery?
- Postgres select distinct row per group without repeating
- How do I (or can I) SELECT DISTINCT on multiple columns?
More questions with similar tag
- How can a PostgreSQL database cease to exist in between two consecutive shell commands?
- How to pg_upgrade with an User-defined Data-Directory
- Storing python lists temporarily in a database for scheduled tasks?
- Getting query by date - ERROR: operator does not exist: timestamp without time zone ~~ unknown
- when running tests in django and PostgreSQL DB can't create database
- Postgres on Docker - stack depth limit exeeded
- PostgreSQL11 xpath query not working properly
- PostgreSQL update time zone offset
- Postgres inner query performance
- Achieving FOR-loop like functionality in Postgresql
- Amazon RDS IAM PAM Auth failed
- Creating a function in Postgresql that does not return composite values
- Use python to execute line in postgresql
- Postgresql trigger on user logon
- SQL Alter table Statement for columns containing data array
- Postgresql : Query were each join result is a line
- Becoming a superuser postgresql
- PSQL Copy CSV data to postgres DB
- psql: could not connect to server: No such file or directory
- How to only keep properties from a Postgres jsonb object that exist in an array of keys?
- PLPGSQL function returns ERROR: integer out of range even if value is correct
- Could not translate host name "db" to address using Postgres, Docker Compose and Psycopg2
- Pass multiple postgres SQL statements in a single PGexec call
- Spring Data JPA + Postgres - Unable to insert data with One to One mapping
- Multiple Database in a postgres cluster on Kubernetes?
- How to create a function with a condition on a column array in pl/pgsql?
- Postgres create table asynchronously
- What does this SQL query replacing JSON text mean?
- PSQL Restore db file with xz
- How to format this PostgreSQL query to be able to run with db:migrate
Source:
stackoverflow.com