Can I Group By Results After Order By

jason310771 · November 3, 2012

I can not seem to get the first query to return the results in 'dateAdded' order. I am after the most recent result in each group to be returned.

It seems that the grouping is done in a different way than I expected it to be done in.

I guess what I am after is the same as my second query, but them to have them grouped after the ORDER BY `c`.`dateAdded` DESC not before, which is what I think is happening?

Can someone give me some clues as to why this might be? or a better solution.

SELECT    `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`,
`i`.`userid` AS `itemOwner`, `i`.`title`,
`c`.`userid` AS `posterId`, `poster`.`fullname` AS `posterName`
FROM `comments` as `c`
LEFT JOIN `items` as `i` ON `i`.`itemid` = `c`.`itemid`
LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
WHERE `c`.`userid` = '" . mysql_real_escape_string($_SESSION['user']['userid']) . "'
GROUP BY `c`.`itemid`
ORDER BY `c`.`dateAdded` DESC

The second one returns the results as expected, in dateAdded order.

SELECT    `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`,
`i`.`userid` AS `itemOwner`, `i`.`title`,
`c`.`userid` AS `posterId`, `poster`.`fullname` AS `posterName`
FROM `comments` as `c`
LEFT JOIN `items` as `i` ON `i`.`itemid` = `c`.`itemid`
LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
WHERE `c`.`itemid` = '" . $getCommentTitle['itemid'] . "'
ORDER BY `c`.`dateAdded` DESC

Barand · November 3, 2012

You could select MAX(dateAdded) which would give the latest date for each item

Barand · November 3, 2012

To get latest comment for each item you first need to find the latest date then match the record with that date


SELECT  `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`,
`i`.`userid` AS `itemOwner`, `i`.`title`,
`c`.`userid` AS `posterId`, `poster`.`fullname` AS `posterName`
FROM `comments` as `c`
INNER JOIN (
   SELECT id, itemid, MAX(dateAdded) as dateAdded
   FROM comments
   GROUP BY id, itemid
   ) as X USING (id, itemid, dateAdded)
LEFT JOIN `items` as `i` ON `i`.`itemid` = `c`.`itemid`
LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
WHERE `c`.`userid` = '" . mysql_real_escape_string($_SESSION['user']['userid']) . "'
GROUP BY `c`.`itemid`
ORDER BY `c`.`dateAdded` DESC

jason310771 · November 3, 2012

Hey, Thanks, I just tried the query you gave but this did not work for me. The results are not in date order.

I know that the second query works just how it should, so think i should use that as a bases for the first query i need, which needs to get the most recent comment for each of the item convasations that the logged in user has taken part in.

so I thinjk what I am after is a way to use the second query but only return one of each itemid where the dateAdded is the most recent date.

Edited November 3, 2012 by Realistic Hostings

Barand · November 3, 2012

my subquery should have used userid rather than id

SELECT  `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`,
`i`.`userid` AS `itemOwner`, `i`.`title`,
`c`.`userid` AS `posterId`, `poster`.`fullname` AS `posterName`
FROM `comments` as `c`
INNER JOIN (
    SELECT userid, itemid, MAX(dateAdded) as dateAdded
    FROM comments
    GROUP BY userid, itemid
    ) as X USING (userid, itemid, dateAdded)
LEFT JOIN `items` as `i` ON `i`.`itemid` = `c`.`itemid`
LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
WHERE `c`.`userid` = '" . mysql_real_escape_string($_SESSION['user']['userid']) . "'
GROUP BY `c`.`itemid`
ORDER BY `c`.`dateAdded` DESC

PFMaBiSmAd · November 3, 2012

If you want the specific row in a group that corresponds to a max (or other specific) value - http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html

jason310771 · November 3, 2012

This still seems to return the same results with the last one in the list not being the latest comment in that item-Comment-Group

jason310771 · November 3, 2012

This is what is shown on my page based on the last query.

They click on a click to show/hide the rest of the comments and the section below it opens up. I have placed *** *** to show these areas.

The first two show correctly. But the last one does not show the correct recent comment, which should be showing...

i380 u204 simon c2

by simon on 2012-11-02 15:52:39 report

it is currently showing the 'second post' for that item.

View Item Comments

i345 u100 4'' title'

1

by Jason

on 2012-11-03 12:29:16

report

click to show/hide aditional comments

***

1

by Jason on 2012-11-03 12:29:16 report

gfhjgfhj

by Jason on 2012-11-03 12:28:17 report

lkk

by Jason on 2012-11-02 22:04:42 report

u101 emma i345 c1

by emma on 2012-11-02 16:07:36 report

***

i368 u100 test t testt test

jasontest

by Jason on 2012-11-02 22:08:16 report

click to show/hide aditional comments

***

jasontest

by Jason on 2012-11-02 22:08:16 report

jjjjjjjjjjj

by James on 2012-11-02 22:07:14 report

***

i380 u100 test t testt test

i380 u100 jason@test.com c2

by Jason on 2012-11-02 15:50:54 report

click to show/hide aditional comments

***

i380 u204 simon c2

by simon on 2012-11-02 15:52:39 report

i380 u204 simon c1

by simon on 2012-11-02 15:52:30 report

i380 u100 jason@test.com c2

by Jason on 2012-11-02 15:50:54 report

i380 u100 jason@test.com c1 e2

by Jason on 2012-11-02 15:50:39 report

Barand · November 3, 2012

All I can say is it worked when it left the factory

COMMENTS

+----+--------+--------+---------------------+-------------+
| id | itemid | userid | dateadded		   | comment	 |
+----+--------+--------+---------------------+-------------+
|  1 |	  1 |	  1 | 2012-10-01 01:30:05 | jfkjfksjd   |
|  2 |	  1 |	  1 | 2012-10-02 01:30:05 | uyoiuy	  |
|  3 |	  2 |	  1 | 2012-10-03 01:30:05 | wert	    |
|  4 |	  2 |	  1 | 2012-10-03 01:30:25 | xcvb	    |
|  5 |	  3 |	  1 | 2012-10-04 01:30:05 | huiniuhiuhn |
|  6 |	  3 |	  1 | 2012-10-05 01:30:05 | rdctrdc	 |
|  7 |	  2 |	  2 | 2012-10-06 01:30:05 | oikop	   |
|  8 |	  1 |	  2 | 2012-10-07 01:30:05 | escfcdfc    |
|  9 |	  1 |	  2 | 2012-10-08 01:30:05 | cxvdtrd	 |
+----+--------+--------+---------------------+-------------+

QUERY

SELECT  `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`,
`c`.`userid` AS `posterId`
FROM `comments` as `c`
INNER JOIN (
   SELECT userid, itemid, MAX(dateAdded) as dateAdded
   FROM comments
   GROUP BY userid, itemid
   ) as X USING (userid, itemid, dateAdded)
WHERE `c`.`userid` = 1
GROUP BY `c`.`itemid`
ORDER BY `c`.`dateAdded` DESC;

RESULTS

+----+---------------------+--------+---------+----------+
| id | dateAdded		   | itemid | comment | posterId |
+----+---------------------+--------+---------+----------+
|  6 | 2012-10-05 01:30:05 |	  3 | rdctrdc |	    1 |
|  4 | 2012-10-03 01:30:25 |	  2 | xcvb    |	    1 |
|  2 | 2012-10-02 01:30:05 |	  1 | uyoiuy  |	    1 |
+----+---------------------+--------+---------+----------+

jason310771 · November 4, 2012

this is getting rather frustrating... lol

ok here are all the tables that i have.... could be something i missed out somethere...

CREATE TABLE IF NOT EXISTS `users` (
 `userid` bigint(20) NOT NULL AUTO_INCREMENT,
 `account_type` varchar(20) NOT NULL DEFAULT 'user',
 `banned` char(3) NOT NULL DEFAULT 'No',
 `shopName` varchar(200) NOT NULL,
 `fullname` varchar(150) NOT NULL,
 `email` varchar(150) NOT NULL,
 `postcode` varchar(10) NOT NULL,
 `password` varchar(50) NOT NULL,
 `verified` varchar(3) NOT NULL DEFAULT 'No',
 `suspended` varchar(3) NOT NULL DEFAULT 'No',
 `verifyCode` varchar(32) NOT NULL,
 PRIMARY KEY (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=206 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`userid`, `account_type`, `banned`, `shopName`, `fullname`, `email`, `postcode`, `password`, `verified`, `suspended`, `verifyCode`) VALUES
(101, 'admin', 'No', 'bits', 'emma', 'emma@test.com', 'abc', '', 'Yes', 'No', ''),
(100, 'Gadmin', 'No', 'Jason', 'Jason', 'jason@test.com', 'abc', '', 'Yes', 'No', ''),
(163, 'user', 'No', 'James', 'James', 'james@test.com', 'abc', '', 'Yes', 'No', ''),
(204, 'Gadmin', 'No', 'simons', 'simon', 'simon@test.com', 'abc', '', 'Yes', 'No', '');





CREATE TABLE IF NOT EXISTS `comments` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `dateAdded` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `itemid` bigint(20) NOT NULL,
 `userid` bigint(20) NOT NULL,
 `comment` text NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=73 ;

--
-- Dumping data for table `comments`
--

INSERT INTO `comments` (`id`, `dateAdded`, `itemid`, `userid`, `comment`) VALUES
(65, '2012-11-02 16:05:59', 349, 101, 'u101 emma i349 c1'),
(66, '2012-11-02 16:06:06', 349, 101, 'u101 emma i349 c2'),
(64, '2012-11-02 15:55:33', 351, 204, 'i351 u204 simon c2'),
(63, '2012-11-02 15:55:21', 351, 204, 'i351 u204 simon c1'),
(62, '2012-11-02 15:52:39', 380, 204, 'i380 u204 simon c2'),
(60, '2012-11-02 15:50:54', 380, 100, 'i380 u100 jason@test.com c2'),
(61, '2012-11-02 15:52:30', 380, 204, 'i380 u204 simon c1'),
(59, '2012-11-02 15:50:39', 380, 100, 'i380 u100 jason@test.com c1 e2'),
(67, '2012-11-02 16:07:36', 345, 101, 'u101 emma i345 c1'),
(68, '2012-11-02 22:04:42', 345, 100, 'lkk'),
(69, '2012-11-02 22:07:14', 368, 163, 'jjjjjjjjjjj'),
(70, '2012-11-02 22:08:16', 368, 100, 'jasontest'),
(71, '2012-11-03 12:28:17', 345, 100, 'gfhjgfhj'),
(72, '2012-11-03 12:29:16', 345, 100, '1');

Barand · November 4, 2012

I took out the item table from the query

SELECT  `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`,
`c`.`userid` AS `posterId`, `poster`.`fullname` AS `posterName`
FROM `comments` as `c`
INNER JOIN (
   SELECT userid, itemid, MAX(dateAdded) as dateAdded
   FROM comments
   GROUP BY userid, itemid
   ) as X USING (userid, itemid, dateAdded)
LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
WHERE `c`.`userid` = 100
GROUP BY `c`.`itemid`
ORDER BY `c`.`dateAdded` DESC;

RESULTS for userid = 100

+----+---------------------+--------+-----------------------------+----------+------------+
| id | dateAdded		   | itemid | comment					 | posterId | posterName |
+----+---------------------+--------+-----------------------------+----------+------------+
| 72 | 2012-11-03 12:29:16 |    345 | 1						   |	  100 | Jason	  |
| 70 | 2012-11-02 22:08:16 |    368 | jasontest				   |	  100 | Jason	  |
| 60 | 2012-11-02 15:50:54 |    380 | i380 u100 jason@test.com c2 |	  100 | Jason	  |
+----+---------------------+--------+-----------------------------+----------+------------+

RESULTS userid = 204

+----+---------------------+--------+--------------------+----------+------------+
| id | dateAdded		   | itemid | comment		    | posterId | posterName |
+----+---------------------+--------+--------------------+----------+------------+
| 64 | 2012-11-02 15:55:33 |    351 | i351 u204 simon c2 |	  204 | simon	  |
| 62 | 2012-11-02 15:52:39 |    380 | i380 u204 simon c2 |	  204 | simon	  |
+----+---------------------+--------+--------------------+----------+------------+

jason310771 · November 4, 2012

ah i think i have worked out where i might be going wrong, it is showing the most recent message of userid '100' in each itemcomment, what i was after is the most recent comment no matter whose comment it was, where userid 100 has taken part in the convosations of that item.

i have re-read this over and over before sending to make sure i am explaining this right.

Barand · November 4, 2012

SELECT `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`,
`c`.`userid` AS `posterId`, `poster`.`fullname` AS `posterName`
FROM `comments` as `c`
INNER JOIN (
SELECT itemid, MAX(dateAdded) as dateAdded
FROM comments
WHERE itemid IN
 (
 SELECT itemid FROM comments
 WHERE userid = 100
 )
GROUP BY itemid
) as X USING (itemid, dateAdded)
LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
ORDER BY `c`.`dateAdded` DESC;

results

+----+---------------------+--------+--------------------+----------+------------+
| id | dateAdded		 | itemid | comment		 | posterId | posterName |
+----+---------------------+--------+--------------------+----------+------------+
| 72 | 2012-11-03 12:29:16 | 345 | 1				 |	 100 | Jason	 |
| 70 | 2012-11-02 22:08:16 | 368 | jasontest		 |	 100 | Jason	 |
| 62 | 2012-11-02 15:52:39 | 380 | i380 u204 simon c2 |	 204 | simon	 |
+----+---------------------+--------+--------------------+----------+------------+

Edited November 4, 2012 by Barand

jason310771 · November 4, 2012

Wow thank you so much Barand, I shall see if I can learn from this query should I need to use something similar in the future. I have only really used the basic function and this one is way over my head at the moment.

Thank again for your help on this. Much appreciated.

Sign In

Can I Group By Results After Order By

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information