Eric Eric - 1 year ago 83
SQL Question

How to get a distinct result ordered by a different column? SQL Postgresql

I'm trying to figure the best way to perform this query in postgresql. I have a messages table and I want to grab the last message a user received from each distinct user. I need to select everything from the row.

I would think this is where I want to group by the senders id "msgfromid", but when I do this it complains that I haven't included everything from my select statement in my group by statement, but I only want to group by the one column, not all of them. So if I try to use Distinct on the one column it forces me to order by the 'distinct on' column first ("msgfromid") which prevents me from being able to get the correct row I need (ordered by the last message sent from the sender "msgsenttime").

My goal is to make this as efficient as possible on my server and database.

This is what I have right now, not working. (This is a sub-query of another query I use to join relevant information afterwards but I figure that is irrelevant)

SELECT DISTINCT ON ("msgfromid") "msgfromid", "msgid", "msgtoid", "msgsenttime", "msgreadtime", "msgcontent", "msgreportstatus", "senderun", "recipientun"
FROM "messages"
WHERE "msgtoid" = ?
ORDER BY "msgsenttime" desc, "msgfromid"

I thought maybe if I pre-ordered them in a sub-query it would work but it just seems to randomly pick one anyway, and this can't be very efficient, even if it were to work, since I'm pulling every message out to begin with, right?:

SELECT DISTINCT ON ("msgfromid") "msgfromid", "msgid", "msgtoid", "msgsenttime", "msgreadtime", "msgcontent", "msgreportstatus", "senderun", "recipientun"
SELECT * FROM "messages"
WHERE "msgtoid" = ?
ORDER BY "msgsenttime" desc
) as "mqo"

Thanks for any help.

Answer Source

Your order by keys are in the wrong order:

SELECT DISTINCT ON ("msgfromid") m.*
FROM "messages" m 
WHERE "msgtoid" = ?
ORDER BY "msgfromid", "msgsenttime" desc;

For DISTINCT ON, the keys in parentheses need to be the first keys in the ORDER BY.

If you want the final result ordered in a different way, then you need to use a subquery, with a different ORDER BY on the outer query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download