49954

Select distinct last messages by pairs in postgres

Question:

I have a table with messages:

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/f7o3M.png" data-original="https://i.stack.imgur.com/f7o3M.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

I need to only one LAST by date and time conversation between two users and here is what i do:

select distinct on (user_from, user_to) * from messages

Here is what I get:

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/2v8Dg.png" data-original="https://i.stack.imgur.com/2v8Dg.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

I do get one unique conversations lines between two users, but its not the last one.

<strong>How should I select only the last one unique conversation between two users?</strong>

EDITED: expected output:

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/BMZRk.png" data-original="https://i.stack.imgur.com/BMZRk.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

Answer1:

Here is how I did it:

SELECT DISTINCT ON (user_from, user_to) * FROM messages ORDER BY user_from, user_to, date+time DESC

Answer2:

You can achieve this using <a href="http://www.postgresql.org/docs/9.3/static/tutorial-window.html" rel="nofollow">Window Functions</a>

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!

Recommend

  • How to delete multiple checked records in rails?
  • How to select and click all html checkboxes?
  • End Conversation in Seam when the user leaves the page
  • Is there a list of all stock messaging apps package names for all Android phone constructors?
  • How to create datalist dynamically
  • How to really understand robocopy return code of 2
  • OleDB selecting multiple CSVs with first row as field names in C#
  • Unable to compute a CMAC using Cygwin and OpenSSL
  • angular js '?' in ng-src causes infinite loop
  • How to list languages supported by my application
  • Find out specific weblogic process running
  • gridview on page won't refresh, even when calling databind again
  • git-svn: automatically importing/creating svn revisions as git tags
  • DataGrid show an empty row when DataTable is empty
  • Java catching exceptions and subclases
  • Do stored procedures have the ability to delete a file from the OS?
  • How to sort a javascript array by date
  • ASP.NET Gridview Paging Problem
  • send data back from jsp iterator to struts action class
  • Slowly changing dimension - What is Pure type 6 implementation
  • SQL append distinct values from two columns and two tables
  • Allocating a 2D contiguous array within a function
  • Hibernate: Inheritance and relationship mapping + generics
  • Should I use composite primary keys in Grails?
  • PHP buffered output depending on server setting?
  • Functions in global context
  • R - Combining Columns to String Based on Logical Match
  • Read text file and split every line in MSBuild
  • Is possible to count alias result on mysql
  • Check if a string to interpolate provides expected placeholders
  • Deserializing XML into class C#
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • Return words with double consecutive letters
  • how to add data labels for bar graph in matlab
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • python draw pie shapes with colour filled
  • How to Embed XSL into XML