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