PostgreSQL does not allow me to group a column with order

distinct, distinct-on, group-by, postgresql

Solution

You want to use the `rank()` window function to order the results within each `useridx` group and then peel off the first one by wrapping the ranked results in a derived table:

select id, useridx, isread, message, date
from (
    select id, useridx, isread, message, date,
           rank() over (partition by useridx order by date desc) as r
    from messages
    where isread = 1
) as dt
where r = 1

That will give your the rows with `id` 2, 3, and 6 from your sample. You might want to add a secondary sort key in the `over` to consistently make a choice when you have multiple messages per `useridx` on the same date.

You'll need at least PostgreSQL 8.4 (AFAIK) to have window functions.

Problem

In PostgreSQL i want to fetch every users at once and order them by date. This is my query: ``` SELECT id, useridx, isread, message, date FROM messages WHERE isread = 1 GROUP BY useridx ORDER BY date DESC ``` This is a sample data: ``` ------------------------------------------------------ + id | useridx | isread | messsage | date + ------------------------------------------------------ 1 | 1 | 0 | Hello | 2012-01-01 2 | 2 | 1 | Hi | 2012-01-02 3 | 3 | 1 | Test | 2012-01-03 4 | 3 | 0 | My Msg | 2012-01-04 5 | 4 | 1 | sadasd | 2012-01-05 6 | 4 | 1 | sdfsdfd | 2012-01-06 7 | 4 | 0 | sdfsdfsd | 2012-01-07 8 | 5 | 0 | 5345634 | 2012-01-08 9 | 6 | 0 | sdfdfsd | 2012-01-09 10 | 7 | 0 | sdfsdfsf | 2012-01-10 ------------------------------------------------------ ``` Now, what i want to do is fetch this table by grouping them via useridx and order by date. Expected Result: ``` ------------------------------------------------------ + id | useridx | isread | messsage | date + ------------------------------------------------------ 6 | 4 | 1 | sdfsdfd | 2012-01-06 3 | 3 | 1 | Test | 2012-01-03 2 | 2 | 1 | Hi | 2012-01-02 ------------------------------------------------------ ``` Actual Result ``` ERROR: column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function ``` I do not want to group date either. I just want to group with useridx and sort them by date DESC. Any help/idea is appreciated! Note: I also tried Distinct. Not fit my needs or i did wrongly. I am very confused and stuck between `DISTINCT ON` and `rank()` methods. Conclusion: For who get the same problem here can read this as an answer. Both @kgrittn's and @mu is too short's answers are correct. I will continue to use both answers and schemas on my project and in time i can understand which one is the best -i guess-. So, pick one of them and continue to your work. You will be just fine. Last Update: Sometimes, Distinct On excludes some ids from result. Lets say i have a id column and i have 6 rows which is same. So, distinct on exlude it from the result BUT rank() just result it. So, use rank()!

Original source