StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPostgreSQL does not allow me to group a column with order
text
Body
copied!<p>In PostgreSQL i want to fetch every users at once and order them by date.</p> <p>This is my query:</p> <pre><code>SELECT id, useridx, isread, message, date FROM messages WHERE isread = 1 GROUP BY useridx ORDER BY date DESC </code></pre> <p>This is a <strong>sample data</strong>:</p> <pre><code>------------------------------------------------------ + 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 ------------------------------------------------------ </code></pre> <p>Now, what i want to do is fetch this table by grouping them via useridx and order by date.</p> <p><strong>Expected Result</strong>:</p> <pre><code>------------------------------------------------------ + 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 ------------------------------------------------------ </code></pre> <p><strong>Actual Result</strong></p> <pre><code>ERROR: column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function </code></pre> <p>I do not want to group date either. I just want to group with useridx and sort them by date DESC.</p> <p>Any help/idea is appreciated!</p> <p><strike>Note: I also tried Distinct. Not fit my needs or i did wrongly.</strike></p> <p>I am <strong>very confused and stuck</strong> between <code>DISTINCT ON</code> and <code>rank()</code> methods.</p> <p><strong>Conclusion</strong>: 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.</p> <p><strong>Last Update</strong>: 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. <strong>So, use rank()!</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload