I want to make a query that will do the following:
1) group all the topicid together (lets call this “group”);
2) within each group sort the rows by parentid ascending, but NULL always is sorted first;
3) groups that are “pinned” are displayed first
4) then groups that have the “latest” post displayed first
So the query should produce the following result:
As you can see, all groups that have the same topicid are together (1), withing each group the the rows are sorted by ascending parentid but NULL always is first (2), the group that is “pinned” are first to be displayed (3), then the group that has the “lastest” post is displayed
1) you really mean ORDER BY — “group” is usually used only for GROUP BY which is a different concept
2) NULLs first is the default MySQL behaviour in ASC sort order
what you want is this —
ORDER
BY topicid ASC -- 1)
, parentid ASC -- 2) with NULLS first by default
, pinned_1 DESC -- 3) DESC to put 'yes' ahead of 'no'
, `date` DESC -- 4)p.s. re: date — please don’t name your columns using reserved words, it requires them to be escaped (in MySQL this is done with backticks)
Thanks for your reply. But I don’t think that your query does what I want it to do.
I don’t know if I can explain this right.
When I speak of “group” I mean that all rows that have the same topicid should always stick together. If you take a look at the “lastest date” of the “group” with topicid 2, then you see that the lastest date is “May 11”. The same for the “group” with topicid 3. But the lastest date of the “group” with topicid 1 is “May 10”. So suppose all the pinned variables are set to “no”. Then the query should display group 2 and group 3 before group 1 because they have a more recent date in their rows then group 1. You understand what I mean?
did you test it?
Yes I did. Take a look at the picture below.
Your query displays group 1 (= rows that have topicid 1) first. This is not correct. Both group 2 and group 3 have posts dating May 11. Group 1 has most recent post dating May 10. So group 2 and group 3 must be displayed before group 1.
Maybe I should explain what the purpose is. I’m creating a forum. When a topic gets a reply, the entire topic (all rows that has the topicid) must be bumbed to the top of the forum. So, pinned topics should always be displayed first, then followed by the topic that has the most recent post date because they are bumbed to the top when they get a reply..
this is not really what you first suggested with your 1) 2) 3) 4)
perhaps i didn’t read it closely enough
or perhaps you didn’t explain it clearly enough
This topic will close 3 months after the last reply.
Suggested Topics
| Topic | Category | Replies | Views | Activity |
|---|---|---|---|---|
| How to create a piano business database | Databases | 5 | 374 | Apr 25 |
| MYSQL Query help | Databases | 14 | 242 | 2d |
| [SOLVED] MySQL query with multiple ANDs for same column | Databases | 10 | 197 | 2d |
| What is enum | Databases | 3 | 209 | 19d |
| How can I write this as a JOIN? | Databases | 7 | 537 | Apr 25 |
Source:-sitepoint.
