I have a table with 4 fields, userid, venueid, comment, dateadded. I want to do a query for a specific venueid that groups the records by userid first and then within that group it sorts records in desc order by dateadded. But, I want to order the users putting the user who entered the most recent comment first, and then have all that users comments in the group displayed before the person that entered the second most recent comment is displayed. Make sense?
so in record order, I want to see records 6, 3, 5, 4, 2, 1
I hope that’s clear. The why for this is users will enter comments about the same subject at different times and it makes sense to see all of a user’s records together and it makes sense to see the most recent comments first.
After some further playing, researching and luck I suppose, I came up with the following, which appears to work.
Is there a better way to do this?
SELECT userid, venueid, comment, dateadded
FROM (
SELECT userid, venueid, comment, dateadded
FROM t2
WHERE venueid =356590
ORDER BY dateadded DESC ) AS t1
GROUP BY userid, dateadded DESC
Okay, I stand corrected. I added some more data and played with it and it in fact is not working correctly.
The subquery ORDER BY is indeed ignored. The GROUP BY DESC does work to order the records in descending order within each group. So given that I can write the query as follows and get the same result.
select venuecommentid, addedby, venuecomment, dateadded
FROM venuecomments
WHERE venueid = 356590
GROUP BY addedby, dateadded DESC
This is where I would like to be as an intermediary step in the query. I have each group together with the records inside each group sorted by dateadded DESC. Now what I would like to do is sort the groups (as they are) by what is effectively the first record in each group’s dataadded value in descending order.
So if I have 3 users, that is 3 groups, and if group 1’s last comment was at 1pm (and is therefore the first record within that group) and group 2’s last comment was at 3pm and group 3’s last comment was at 2:30 pm, I want to sort the groups as 2-3-1.
SELECT
t2.userid
, t2.venueid
, t2.comment
, t2.dateadded
FROM
(SELECT
userid
, MAX(dateadded) as maxdateadded
FROM t2
WHERE venueid = 356590
GROUP BY userid
) as t1
INNER JOIN t2
ON t1.userid = t2.userid
WHERE t2.venueid = 356590
ORDER BY
t1.maxdateadded DESC
, t2.userid
, t2.dateadded DESC
when you say GROUP BY addedby, dateadded you are in effect collapsing muiltiple rows into one row per group
that’s the purpose of GROUP BY, to perform an aggregation
i get the impression that you’re interested only in sorting, not collapsing/aggregating data in a group of rows
as to your requirement, i think it’s overly complicated (i mean, just look at the trouble you have explaining it) – have you considered how this complication is going to be received by your users? aren;t you worried about confusing them?
imagine if the weird sorting rules you’re looking for were implemented in this forum…
Understand, but this forum is a running conversation. My situation is more of a snippets type of converstion that are best taken as a whole for each user, rather than just running them in order entered. They have no interaction with each other regarding comments made, i.e. one user is not responding to a comment made by another.
But thanks for the concern and your point is well taken and I will watch for that going forward. In any event, now I have the choice of doing it either so we’ll see if my assumptions are correct.
