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()!
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.