Jump to content

Can I Group By Results After Order By

jason310771

By jason310771
in MySQL Help

 Share

Recommended Posts

jason310771 Member

jason310771

Posted
Posted

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

Link to comment
https://forums.phpfreaks.com/topic/270245-can-i-group-by-results-after-order-by/
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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 Member

jason310771

Posted
  • Author
Posted (edited)

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 by Realistic Hostings
Barand Prolific Member

Barand

Posted
Posted

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

jason310771 Member

jason310771

Posted
  • Author
Posted

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 Prolific Member

Barand

Posted
Posted

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 Member

jason310771

Posted
  • Author
Posted

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 Prolific Member

Barand

Posted
Posted

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 Member

jason310771

Posted
  • Author
Posted

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 Prolific Member

Barand

Posted
Posted (edited) 

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 by Barand
jason310771 Member

jason310771

Posted
  • Author
Posted

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.