PostgreSQL does not allow me to group a column with order

Question

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

Answer 1

PostgreSQL, unlike MySQL, does not show random data for columns which are not aggregated in an aggregated query.

The solution is in the error message

ERROR:  column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function

Which means you must GROUP BY the "messages.date" column or use an aggregate function like MIN() or MAX() when selection this column

Example:

SELECT MIN(id), useridx, isread, message, MAX(date)
FROM messages WHERE isread = 1 
GROUP BY useridx, isread, message
ORDER BY MAX(date) DESC

Answer 2

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.