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.

Answer 3

Another option is to use SELECT DISTINCT ON (which is very different from a simple SELECT DISTINCT):

SELECT *
  FROM (SELECT DISTINCT ON (useridx)
            id, useridx, isread, message, date
          FROM messages
          WHERE isread = 1
          ORDER BY useridx, date DESC) x
  ORDER BY date DESC;

In some cases this can scale better than the other approaches.

Answer 4

Years later, but can't you just order in the FROM subquery:

SELECT m.id, m.useridx, m.isread, m.message, m.date
FROM (
   SELECT m2.id, m2.useridx, m2.isread, m2.message, m2.date 
   FROM message m2 
   ORDER BY m2.id ASC, m2.date DESC
) m
WHERE isread = 1
GROUP BY useridx

This works for me in PostgreSQL 9.2

Answer 5

You are aggregating results.

This means that instead of 2 rows for user 3 you will have just one row. But you also select id, message, isread columns for the aggregated row. How PostgreSQL is supposed to deliver this data? Should it be max() of possible values? Maybe min()?

I assume, that you'd like to have the data on the newest messages. Try this query:

SELECT id, useridx, isread, message, date FROM messages
 WHERE isread = 1 AND (useridx, date) IN
  (SELECT useridx, max(date) FROM messages WHERE isread = 1 GROUP BY useridx);