Jenz
Reputation: 8369
Group by for multiple fields in MYSQL
In my project, I am storing user activities in a table useractivitylog. In one page I am displaying activities of logged in user's friends. I am using the following query for that.
$data['activityquery']=$this->db->query("
SELECT ua.*
, rf.opid
, aup.user_id
, aup.first_name
, aup.last_name
, aup.profileimage
FROM useractivitylog ua
JOIN requestfriend rf
ON ua.userid IN(rf.userid,rf.friendid)
JOIN auth_user_profiles aup
ON aup.user_id = IF(rf.userid=$userid,rf.friendid,rf.userid)
WHERE $userid IN(rf.userid,rf.friendid)
AND rf.status = 2
AND ua.userid != $userid
ORDER
BY ua.date DESC;
");
This returns records as shown in below image.
I want to group the result on activity_type and activity_id. ie, as you can see, some records are repeating as the same activity is done in different times. I want to show only the latest record of the repeating entries.
I tried with
SELECT ua . * , rf.opid, aup.user_id, aup.first_name, aup.last_name, aup.profileimage
FROM useractivitylog AS ua
INNER JOIN requestfriend AS rf ON ( ua.userid = rf.userid
OR ua.userid = rf.friendid )
INNER JOIN auth_user_profiles AS aup ON aup.user_id = IF( rf.userid = '1', rf.friendid, rf.userid )
WHERE (
rf.userid =1
OR rf.friendid =1
)
AND rf.status = '2'
AND ua.userid !=1
GROUP BY ua.activity_type, ua.activity_id
This is giving me correct records.
But I also want it to be in descending order of date while grouping among the repeating records and also for the entire activities ie, the resultant records should also be in date order. Can anyone help me to solve this.
Thanks in advance.
DATABASE
CREATE TABLE `useractivitylog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`activity_type` smallint(6) NOT NULL COMMENT '1-status 2-watch video 3-like 4-dislike 5-starring 6-upload 7-review',
`activity_id` int(11) NOT NULL,
`date` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=236 ;
INSERT INTO `useractivitylog` VALUES(1, 1, 2, 2, 1399959912);
INSERT INTO `useractivitylog` VALUES(2, 1, 4, 2, 1400062813);
INSERT INTO `useractivitylog` VALUES(3, 1, 3, 2, 1400062815);
INSERT INTO `useractivitylog` VALUES(4, 1, 4, 2, 1400062819);
INSERT INTO `useractivitylog` VALUES(5, 1, 7, 3, 1400127388);
INSERT INTO `useractivitylog` VALUES(6, 1, 7, 3, 1400127407);
INSERT INTO `useractivitylog` VALUES(7, 1, 7, 3, 1400127667);
INSERT INTO `useractivitylog` VALUES(8, 1, 7, 3, 1400127870);
INSERT INTO `useractivitylog` VALUES(9, 1, 7, 3, 1400127933);
INSERT INTO `useractivitylog` VALUES(10, 1, 7, 3, 1400127965);
INSERT INTO `useractivitylog` VALUES(11, 1, 7, 3, 1400127978);
INSERT INTO `useractivitylog` VALUES(12, 1, 7, 3, 1400128004);
INSERT INTO `useractivitylog` VALUES(13, 1, 7, 3, 1400128022);
INSERT INTO `useractivitylog` VALUES(14, 1, 2, 3, 1400138835);
INSERT INTO `useractivitylog` VALUES(15, 1, 2, 3, 1400138923);
INSERT INTO `useractivitylog` VALUES(16, 1, 2, 3, 1400139042);
INSERT INTO `useractivitylog` VALUES(17, 1, 2, 3, 1400139044);
INSERT INTO `useractivitylog` VALUES(18, 1, 4, 3, 1400139044);
INSERT INTO `useractivitylog` VALUES(19, 1, 2, 3, 1400139046);
INSERT INTO `useractivitylog` VALUES(20, 1, 2, 3, 1400139047);
INSERT INTO `useractivitylog` VALUES(21, 1, 3, 3, 1400139049);
INSERT INTO `useractivitylog` VALUES(22, 1, 2, 3, 1400139050);
INSERT INTO `useractivitylog` VALUES(23, 1, 2, 3, 1400139052);
INSERT INTO `useractivitylog` VALUES(24, 1, 2, 3, 1400139065);
INSERT INTO `useractivitylog` VALUES(25, 1, 2, 3, 1400139169);
INSERT INTO `useractivitylog` VALUES(26, 1, 5, 3, 1400147330);
INSERT INTO `useractivitylog` VALUES(27, 2, 2, 2, 1400149323);
INSERT INTO `useractivitylog` VALUES(28, 2, 2, 3, 1400149331);
INSERT INTO `useractivitylog` VALUES(29, 2, 2, 3, 1400149357);
INSERT INTO `useractivitylog` VALUES(30, 2, 2, 3, 1400149509);
INSERT INTO `useractivitylog` VALUES(31, 1, 2, 1, 1400215110);
INSERT INTO `useractivitylog` VALUES(32, 1, 2, 1, 1400215345);
INSERT INTO `useractivitylog` VALUES(33, 1, 2, 2, 1400215350);
INSERT INTO `useractivitylog` VALUES(34, 1, 2, 2, 1400215675);
INSERT INTO `useractivitylog` VALUES(35, 1, 2, 2, 1400217939);
INSERT INTO `useractivitylog` VALUES(36, 1, 2, 2, 1400218174);
INSERT INTO `useractivitylog` VALUES(37, 1, 2, 2, 1400239850);
INSERT INTO `useractivitylog` VALUES(38, 1, 2, 2, 1400240532);
INSERT INTO `useractivitylog` VALUES(39, 1, 2, 2, 1400240562);
INSERT INTO `useractivitylog` VALUES(40, 1, 2, 2, 1400240607);
INSERT INTO `useractivitylog` VALUES(41, 1, 2, 1, 1400240610);
INSERT INTO `useractivitylog` VALUES(42, 1, 2, 1, 1400240747);
INSERT INTO `useractivitylog` VALUES(43, 1, 2, 1, 1400243642);
INSERT INTO `useractivitylog` VALUES(44, 1, 2, 1, 1400303660);
INSERT INTO `useractivitylog` VALUES(45, 1, 2, 1, 1400309276);
INSERT INTO `useractivitylog` VALUES(46, 1, 2, 1, 1400309287);
INSERT INTO `useractivitylog` VALUES(47, 1, 2, 1, 1400309851);
INSERT INTO `useractivitylog` VALUES(48, 1, 2, 1, 1400309886);
INSERT INTO `useractivitylog` VALUES(49, 1, 6, 8, 1400312651);
INSERT INTO `useractivitylog` VALUES(50, 28, 2, 8, 1400556893);
INSERT INTO `useractivitylog` VALUES(51, 1, 2, 2, 1400672617);
INSERT INTO `useractivitylog` VALUES(52, 1, 2, 2, 1400672713);
INSERT INTO `useractivitylog` VALUES(53, 1, 2, 2, 1400672739);
INSERT INTO `useractivitylog` VALUES(54, 1, 2, 2, 1400672762);
INSERT INTO `useractivitylog` VALUES(55, 1, 2, 2, 1400672875);
INSERT INTO `useractivitylog` VALUES(56, 1, 2, 2, 1400672912);
INSERT INTO `useractivitylog` VALUES(57, 1, 2, 2, 1400672929);
INSERT INTO `useractivitylog` VALUES(58, 1, 2, 2, 1400672978);
INSERT INTO `useractivitylog` VALUES(59, 1, 2, 2, 1400676486);
INSERT INTO `useractivitylog` VALUES(60, 1, 2, 2, 1400676621);
INSERT INTO `useractivitylog` VALUES(61, 1, 2, 2, 1400676644);
INSERT INTO `useractivitylog` VALUES(62, 1, 2, 2, 1400676657);
INSERT INTO `useractivitylog` VALUES(63, 1, 2, 2, 1400731951);
INSERT INTO `useractivitylog` VALUES(64, 1, 2, 8, 1400734855);
INSERT INTO `useractivitylog` VALUES(65, 1, 2, 8, 1400734885);
INSERT INTO `useractivitylog` VALUES(66, 1, 2, 8, 1400734962);
INSERT INTO `useractivitylog` VALUES(67, 1, 2, 8, 1400734980);
INSERT INTO `useractivitylog` VALUES(68, 1, 2, 8, 1400734984);
INSERT INTO `useractivitylog` VALUES(69, 1, 2, 8, 1400735391);
INSERT INTO `useractivitylog` VALUES(70, 1, 2, 8, 1400737406);
INSERT INTO `useractivitylog` VALUES(71, 1, 2, 8, 1400737448);
INSERT INTO `useractivitylog` VALUES(72, 1, 2, 8, 1400737499);
INSERT INTO `useractivitylog` VALUES(73, 1, 2, 8, 1400739069);
INSERT INTO `useractivitylog` VALUES(74, 1, 2, 8, 1400739164);
INSERT INTO `useractivitylog` VALUES(75, 1, 2, 8, 1400739298);
INSERT INTO `useractivitylog` VALUES(76, 1, 5, 8, 1400739432);
INSERT INTO `useractivitylog` VALUES(77, 1, 2, 8, 1400739435);
INSERT INTO `useractivitylog` VALUES(78, 1, 2, 8, 1400739473);
INSERT INTO `useractivitylog` VALUES(79, 1, 2, 8, 1400739511);
INSERT INTO `useractivitylog` VALUES(80, 1, 2, 8, 1400740212);
INSERT INTO `useractivitylog` VALUES(81, 1, 2, 8, 1400740373);
INSERT INTO `useractivitylog` VALUES(82, 1, 5, 8, 1400740375);
INSERT INTO `useractivitylog` VALUES(83, 1, 2, 8, 1400740378);
INSERT INTO `useractivitylog` VALUES(84, 1, 2, 8, 1400740436);
INSERT INTO `useractivitylog` VALUES(85, 1, 5, 8, 1400740438);
INSERT INTO `useractivitylog` VALUES(86, 1, 5, 7, 1400740445);
INSERT INTO `useractivitylog` VALUES(87, 1, 3, 7, 1400740493);
INSERT INTO `useractivitylog` VALUES(88, 1, 2, 8, 1400740587);
INSERT INTO `useractivitylog` VALUES(89, 1, 3, 8, 1400740589);
INSERT INTO `useractivitylog` VALUES(90, 1, 2, 8, 1400740591);
INSERT INTO `useractivitylog` VALUES(91, 1, 2, 8, 1400740594);
INSERT INTO `useractivitylog` VALUES(92, 1, 2, 8, 1400740606);
INSERT INTO `useractivitylog` VALUES(93, 1, 3, 8, 1400740607);
INSERT INTO `useractivitylog` VALUES(94, 1, 2, 8, 1400740609);
INSERT INTO `useractivitylog` VALUES(95, 1, 2, 2, 1400757648);
INSERT INTO `useractivitylog` VALUES(96, 1, 2, 2, 1400817737);
INSERT INTO `useractivitylog` VALUES(97, 1, 1, 22, 1400842234);
INSERT INTO `useractivitylog` VALUES(98, 1, 2, 2, 1400903181);
INSERT INTO `useractivitylog` VALUES(99, 1, 2, 2, 1400914246);
INSERT INTO `useractivitylog` VALUES(100, 1, 2, 8, 1400914251);
INSERT INTO `useractivitylog` VALUES(101, 1, 2, 2, 1400931063);
INSERT INTO `useractivitylog` VALUES(102, 1, 2, 2, 1400931873);
INSERT INTO `useractivitylog` VALUES(103, 1, 2, 8, 1400931898);
INSERT INTO `useractivitylog` VALUES(104, 1, 2, 2, 1400931902);
INSERT INTO `useractivitylog` VALUES(105, 1, 2, 4, 1401181831);
INSERT INTO `useractivitylog` VALUES(106, 1, 2, 4, 1401181916);
INSERT INTO `useractivitylog` VALUES(107, 1, 2, 4, 1401181943);
INSERT INTO `useractivitylog` VALUES(108, 1, 2, 4, 1401182004);
INSERT INTO `useractivitylog` VALUES(109, 1, 2, 4, 1401182060);
INSERT INTO `useractivitylog` VALUES(110, 1, 2, 4, 1401182210);
INSERT INTO `useractivitylog` VALUES(111, 1, 2, 4, 1401182379);
INSERT INTO `useractivitylog` VALUES(112, 1, 2, 4, 1401182933);
INSERT INTO `useractivitylog` VALUES(113, 1, 2, 4, 1401183001);
INSERT INTO `useractivitylog` VALUES(114, 1, 2, 1, 1401183534);
INSERT INTO `useractivitylog` VALUES(115, 10, 2, 2, 1401268400);
INSERT INTO `useractivitylog` VALUES(116, 4, 1, 23, 1401268555);
INSERT INTO `useractivitylog` VALUES(117, 4, 1, 24, 1401268696);
INSERT INTO `useractivitylog` VALUES(118, 1, 2, 7, 1401347569);
INSERT INTO `useractivitylog` VALUES(119, 1, 2, 8, 1401360692);
INSERT INTO `useractivitylog` VALUES(120, 1, 2, 2, 1401421731);
INSERT INTO `useractivitylog` VALUES(121, 1, 2, 2, 1401423782);
INSERT INTO `useractivitylog` VALUES(122, 1, 2, 2, 1401423865);
INSERT INTO `useractivitylog` VALUES(123, 1, 2, 2, 1401424526);
INSERT INTO `useractivitylog` VALUES(124, 1, 2, 2, 1401424684);
INSERT INTO `useractivitylog` VALUES(125, 1, 2, 2, 1401424751);
INSERT INTO `useractivitylog` VALUES(126, 1, 2, 2, 1401424816);
INSERT INTO `useractivitylog` VALUES(127, 1, 2, 2, 1401424823);
INSERT INTO `useractivitylog` VALUES(128, 1, 2, 2, 1401424876);
INSERT INTO `useractivitylog` VALUES(129, 1, 2, 2, 1401424879);
INSERT INTO `useractivitylog` VALUES(130, 1, 2, 2, 1401424931);
INSERT INTO `useractivitylog` VALUES(131, 1, 2, 2, 1401425095);
INSERT INTO `useractivitylog` VALUES(132, 1, 2, 2, 1401425103);
INSERT INTO `useractivitylog` VALUES(133, 1, 2, 2, 1401425114);
INSERT INTO `useractivitylog` VALUES(134, 1, 2, 2, 1401425154);
INSERT INTO `useractivitylog` VALUES(135, 1, 2, 2, 1401425157);
INSERT INTO `useractivitylog` VALUES(136, 1, 2, 2, 1401425181);
INSERT INTO `useractivitylog` VALUES(137, 1, 2, 2, 1401425192);
INSERT INTO `useractivitylog` VALUES(138, 1, 2, 2, 1401425202);
INSERT INTO `useractivitylog` VALUES(139, 1, 2, 2, 1401425213);
INSERT INTO `useractivitylog` VALUES(140, 1, 2, 2, 1401425254);
INSERT INTO `useractivitylog` VALUES(141, 1, 2, 2, 1401425323);
INSERT INTO `useractivitylog` VALUES(142, 1, 2, 2, 1401425372);
INSERT INTO `useractivitylog` VALUES(143, 1, 2, 2, 1401425384);
INSERT INTO `useractivitylog` VALUES(144, 1, 2, 2, 1401425401);
INSERT INTO `useractivitylog` VALUES(145, 1, 2, 2, 1401425440);
INSERT INTO `useractivitylog` VALUES(146, 1, 2, 2, 1401425910);
INSERT INTO `useractivitylog` VALUES(147, 1, 2, 2, 1401426717);
INSERT INTO `useractivitylog` VALUES(148, 1, 2, 7, 1401430184);
INSERT INTO `useractivitylog` VALUES(149, 1, 7, 7, 1401430211);
INSERT INTO `useractivitylog` VALUES(150, 1, 2, 2, 1401432585);
INSERT INTO `useractivitylog` VALUES(151, 1, 2, 2, 1401432653);
INSERT INTO `useractivitylog` VALUES(152, 1, 2, 2, 1401454284);
INSERT INTO `useractivitylog` VALUES(153, 1, 2, 2, 1401509582);
INSERT INTO `useractivitylog` VALUES(154, 1, 2, 8, 1401519962);
INSERT INTO `useractivitylog` VALUES(155, 1, 2, 2, 1401535507);
INSERT INTO `useractivitylog` VALUES(156, 1, 2, 2, 1401535740);
INSERT INTO `useractivitylog` VALUES(157, 1, 2, 8, 1401536035);
INSERT INTO `useractivitylog` VALUES(158, 1, 2, 8, 1401536072);
INSERT INTO `useractivitylog` VALUES(159, 1, 2, 8, 1401536086);
INSERT INTO `useractivitylog` VALUES(160, 1, 2, 8, 1401536090);
INSERT INTO `useractivitylog` VALUES(161, 1, 4, 8, 1401536091);
INSERT INTO `useractivitylog` VALUES(162, 1, 2, 8, 1401536096);
INSERT INTO `useractivitylog` VALUES(163, 1, 3, 8, 1401536099);
INSERT INTO `useractivitylog` VALUES(164, 1, 2, 8, 1401536103);
INSERT INTO `useractivitylog` VALUES(165, 1, 4, 8, 1401536108);
INSERT INTO `useractivitylog` VALUES(166, 1, 2, 8, 1401536108);
INSERT INTO `useractivitylog` VALUES(167, 1, 2, 8, 1401536116);
INSERT INTO `useractivitylog` VALUES(168, 1, 2, 8, 1401536124);
INSERT INTO `useractivitylog` VALUES(169, 1, 2, 8, 1401536130);
INSERT INTO `useractivitylog` VALUES(170, 1, 2, 8, 1401536139);
INSERT INTO `useractivitylog` VALUES(171, 1, 2, 8, 1401536145);
INSERT INTO `useractivitylog` VALUES(172, 1, 2, 8, 1401541279);
INSERT INTO `useractivitylog` VALUES(173, 1, 3, 3, 1401555253);
INSERT INTO `useractivitylog` VALUES(174, 1, 4, 3, 1401555256);
INSERT INTO `useractivitylog` VALUES(175, 1, 3, 3, 1401555257);
INSERT INTO `useractivitylog` VALUES(176, 1, 2, 16, 1401771312);
INSERT INTO `useractivitylog` VALUES(177, 31, 1, 25, 1401781021);
INSERT INTO `useractivitylog` VALUES(178, 31, 6, 17, 1401781574);
INSERT INTO `useractivitylog` VALUES(179, 31, 2, 16, 1401781752);
INSERT INTO `useractivitylog` VALUES(180, 31, 3, 16, 1401781822);
INSERT INTO `useractivitylog` VALUES(181, 31, 2, 16, 1401781827);
INSERT INTO `useractivitylog` VALUES(182, 31, 2, 16, 1401781833);
INSERT INTO `useractivitylog` VALUES(183, 31, 7, 16, 1401781882);
INSERT INTO `useractivitylog` VALUES(184, 31, 2, 12, 1401781950);
INSERT INTO `useractivitylog` VALUES(185, 31, 2, 16, 1401782018);
INSERT INTO `useractivitylog` VALUES(186, 31, 2, 16, 1401782331);
INSERT INTO `useractivitylog` VALUES(187, 31, 2, 12, 1401788551);
INSERT INTO `useractivitylog` VALUES(188, 31, 2, 8, 1401788646);
INSERT INTO `useractivitylog` VALUES(189, 31, 6, 18, 1401792941);
INSERT INTO `useractivitylog` VALUES(190, 31, 2, 7, 1401793683);
INSERT INTO `useractivitylog` VALUES(191, 31, 4, 1, 1401793773);
INSERT INTO `useractivitylog` VALUES(192, 31, 2, 2, 1401793907);
INSERT INTO `useractivitylog` VALUES(193, 31, 2, 2, 1401793982);
INSERT INTO `useractivitylog` VALUES(194, 31, 2, 2, 1401794066);
INSERT INTO `useractivitylog` VALUES(195, 31, 3, 2, 1401794092);
INSERT INTO `useractivitylog` VALUES(196, 31, 2, 2, 1401794097);
INSERT INTO `useractivitylog` VALUES(197, 31, 2, 2, 1401794472);
INSERT INTO `useractivitylog` VALUES(198, 31, 2, 7, 1401794552);
INSERT INTO `useractivitylog` VALUES(199, 31, 2, 8, 1401795205);
INSERT INTO `useractivitylog` VALUES(200, 31, 7, 8, 1401795222);
INSERT INTO `useractivitylog` VALUES(201, 31, 7, 8, 1401795226);
INSERT INTO `useractivitylog` VALUES(202, 31, 2, 8, 1401795257);
INSERT INTO `useractivitylog` VALUES(203, 28, 2, 8, 1401798261);
INSERT INTO `useractivitylog` VALUES(204, 28, 3, 8, 1401798264);
INSERT INTO `useractivitylog` VALUES(205, 28, 2, 8, 1401798269);
INSERT INTO `useractivitylog` VALUES(206, 10, 2, 2, 1401872961);
INSERT INTO `useractivitylog` VALUES(207, 10, 2, 2, 1401872973);
INSERT INTO `useractivitylog` VALUES(208, 10, 2, 2, 1401872987);
INSERT INTO `useractivitylog` VALUES(209, 10, 2, 2, 1401872999);
INSERT INTO `useractivitylog` VALUES(210, 10, 2, 2, 1401873011);
INSERT INTO `useractivitylog` VALUES(211, 10, 2, 2, 1401875866);
INSERT INTO `useractivitylog` VALUES(212, 1, 6, 19, 1402056888);
INSERT INTO `useractivitylog` VALUES(213, 1, 2, 1, 1402087162);
INSERT INTO `useractivitylog` VALUES(214, 1, 2, 19, 1402088190);
INSERT INTO `useractivitylog` VALUES(215, 1, 2, 14, 1402088315);
INSERT INTO `useractivitylog` VALUES(216, 1, 2, 2, 1402091176);
INSERT INTO `useractivitylog` VALUES(217, 1, 2, 18, 1402094939);
INSERT INTO `useractivitylog` VALUES(218, 1, 2, 11, 1402094968);
INSERT INTO `useractivitylog` VALUES(219, 1, 2, 14, 1402094974);
INSERT INTO `useractivitylog` VALUES(220, 1, 2, 11, 1402094997);
INSERT INTO `useractivitylog` VALUES(221, 1, 2, 18, 1402095000);
INSERT INTO `useractivitylog` VALUES(222, 1, 2, 16, 1402095042);
INSERT INTO `useractivitylog` VALUES(223, 31, 2, 19, 1402139568);
INSERT INTO `useractivitylog` VALUES(224, 31, 2, 19, 1402139663);
INSERT INTO `useractivitylog` VALUES(225, 31, 2, 18, 1402139696);
INSERT INTO `useractivitylog` VALUES(226, 31, 2, 11, 1402139735);
INSERT INTO `useractivitylog` VALUES(227, 31, 2, 11, 1402139762);
INSERT INTO `useractivitylog` VALUES(228, 31, 7, 11, 1402139786);
INSERT INTO `useractivitylog` VALUES(229, 31, 2, 11, 1402139834);
INSERT INTO `useractivitylog` VALUES(230, 31, 2, 11, 1402139863);
INSERT INTO `useractivitylog` VALUES(231, 31, 6, 20, 1402140324);
INSERT INTO `useractivitylog` VALUES(232, 3, 1, 26, 1402330720);
INSERT INTO `useractivitylog` VALUES(233, 3, 3, 4, 1402331176);
INSERT INTO `useractivitylog` VALUES(234, 1, 2, 30, 1402342251);
INSERT INTO `useractivitylog` VALUES(235, 1, 2, 16, 1402393661);
CREATE TABLE `requestfriend` (
`opid` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`friendid` int(11) NOT NULL,
`requesttime` int(11) NOT NULL,
`message` varchar(256) NOT NULL,
`source` smallint(6) NOT NULL COMMENT '1- Gmail 2- FB',
`requesttype` smallint(6) NOT NULL COMMENT '0 - Friend 1 - Invitation',
`friendemail` varchar(100) NOT NULL,
`status` smallint(6) NOT NULL COMMENT '1-send 2-accepted 3-rejected 4-unsubscribed',
PRIMARY KEY (`opid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Friend request/Invitations' AUTO_INCREMENT=26 ;
INSERT INTO `requestfriend` VALUES(15, 1, 0, 1394618441, '', 1, 1, '[email protected]', 1);
INSERT INTO `requestfriend` VALUES(16, 1, 0, 1394618441, '', 1, 1, '[email protected]', 1);
INSERT INTO `requestfriend` VALUES(17, 4, 1, 1395052826, 'Hi, add me as your friend', 0, 0, '[email protected]', 2);
INSERT INTO `requestfriend` VALUES(18, 11, 1, 1395054447, 'Hi, add me as your friend', 0, 0, '[email protected]', 2);
INSERT INTO `requestfriend` VALUES(19, 1, 10, 1395054521, 'Hi, add me as your friend', 0, 0, '[email protected]', 2);
INSERT INTO `requestfriend` VALUES(20, 2, 1, 1400148560, 'Hi, add me as your friend', 0, 0, '[email protected]', 2);
INSERT INTO `requestfriend` VALUES(21, 31, 28, 1401780582, 'Hi, add me as your friend', 0, 0, '[email protected]', 2);
INSERT INTO `requestfriend` VALUES(22, 29, 1, 1402313081, 'Hi, add me as your friend', 0, 0, '[email protected]', 2);
INSERT INTO `requestfriend` VALUES(23, 29, 10, 1402313089, 'Hi, add me as your friend', 0, 0, '[email protected]', 1);
INSERT INTO `requestfriend` VALUES(24, 29, 3, 1402313119, 'Hi, add me as your friend', 0, 0, '[email protected]', 2);
INSERT INTO `requestfriend` VALUES(25, 29, 4, 1402313762, 'Hi, add me as your friend', 0, 0, '[email protected]', 2);
CREATE TABLE `auth_user_profiles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`birthdate` int(11) NOT NULL,
`relationship` smallint(6) NOT NULL COMMENT '1-single 2-married 3-Engaged 4- Separated 5- Divorced 6-Other',
`address` varchar(255) NOT NULL,
`city` varchar(50) NOT NULL,
`zipcode` int(11) NOT NULL,
`phone_no` char(12) NOT NULL,
`country` int(11) NOT NULL,
`work` varchar(255) NOT NULL,
`registeredip` char(15) NOT NULL,
`registerdate` int(11) NOT NULL,
`profileimage` varchar(200) NOT NULL DEFAULT 'default',
`gender` smallint(6) NOT NULL,
`profession` varchar(50) NOT NULL,
`aboutme` varchar(250) NOT NULL,
`referral_balance` decimal(10,2) NOT NULL,
`website` varchar(255) DEFAULT NULL,
`google_open_id` varchar(256) DEFAULT NULL,
`yahoo_open_id` varchar(256) DEFAULT NULL,
`facebook_id` varchar(256) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;
INSERT INTO `auth_user_profiles` VALUES(1, 1, 'Jeena', 'Mariam', 638649000, 1, 'fghfllgdfg', 'kochi', 9887888, '25896589518', 67, 'NNNooo', '127.0.0.1', 1393409056, 'cind.jpg', 1, '', 'sssssssppp', 2022.09, NULL, 'https://www.google.com/accounts/o8/id?id=AItOawlUhpWgJhYxjlgg8UjhsKR0u40IvYQrGZ0', 'https://me.yahoo.com/a/Yl_iKDxqkIJIvZ7y5KHTBdkNDw2L#79ed5', NULL);
INSERT INTO `auth_user_profiles` VALUES(2, 2, 'Kichu', 'K', 0, 0, '', '', 0, '8958698565', 20, '', '127.0.0.1', 1394014161, 'default', 0, '', '', 24.02, NULL, NULL, NULL, NULL);
INSERT INTO `auth_user_profiles` VALUES(3, 3, 'Nihal', 'Krishna', 0, 0, '', '', 0, '9878956895', 50, '', '127.0.0.1', 1394014661, 'default', 0, '', '', 0.00, NULL, NULL, NULL, NULL);
INSERT INTO `auth_user_profiles` VALUES(4, 4, 'Nithal', 'John', 0, 0, '', 'Kochi', 0, '8789565456', 15, '', '127.0.0.1', 1394083479, 'default', 0, '', '', 0.00, NULL, NULL, NULL, NULL);
INSERT INTO `auth_user_profiles` VALUES(5, 9, 'Krishna', 'K', 0, 0, '', 'Bangalore', 0, '98711111111', 26, '', '127.0.0.1', 1395051693, 'default', 0, '', '', 0.00, NULL, NULL, NULL, NULL);
INSERT INTO `auth_user_profiles` VALUES(6, 10, 'Jithin', 'Mathew', 0, 0, '', 'Bangalore', 0, '98711111111', 22, '', '127.0.0.1', 1395052731, 'default', 0, '', '', 0.00, NULL, NULL, NULL, NULL);
INSERT INTO `auth_user_profiles` VALUES(21, 25, 'sdfsd', 'rrrr', 0, 0, '', 'gfdg', 0, '98711111111', 62, '', '192.168.1.150', 1398412578, 'default', 0, '', '', 108.00, NULL, NULL, NULL, NULL);
INSERT INTO `auth_user_profiles` VALUES(23, 27, 'lol', 'lol', 0, 0, '', 'jdjdjd', 0, '8383838383', 19, '', '192.168.1.150', 1399607669, 'default', 0, '', '', 8.00, NULL, NULL, NULL, NULL);
INSERT INTO `auth_user_profiles` VALUES(24, 28, 'Milan', 'Milan', 0, 0, '', 'Kottayam', 0, '9656544664', 26, '', '192.168.1.151', 1400556770, 'default', 0, '', '', 30.00, NULL, NULL, NULL, NULL);
INSERT INTO `auth_user_profiles` VALUES(25, 29, 'praveen', 'pp', 0, 0, '', 'xzxxxxc', 0, '98711111111', 18, '', '192.168.1.150', 1400579597, 'default', 0, '', '', 8.00, NULL, NULL, NULL, NULL);
INSERT INTO `auth_user_profiles` VALUES(27, 31, 'Alen', 'Jose', 619426800, 1, 'goodwill,\nhardwork,\npraywell\n', 'Kottayam', 0, '9745460905', 67, 'no workk', '117.222.144.62', 1401780167, 'astra_logo_a.gif', 1, '', 'new to this site . seems to good in features.', 2722.01, NULL, NULL, NULL, NULL);
INSERT INTO `auth_user_profiles` VALUES(28, 32, 'Roshan', 'Rony', 0, 0, '', 'Delhi', 0, '98711111111', 26, '', '61.3.167.211', 1401954757, 'default', 0, '', '', 10.00, NULL, NULL, NULL, NULL);
Upvotes: 2
Views: 119
Answers (1)
M Khalid Junaid
Reputation: 64466
You need to add another join that will be a self join to your useractivitylog table, this will join the row with maximum date per activity_type,activity_id group,see subquery with alias ua_new
SELECT
ua.*,
rf.opid,
aup.user_id,
aup.first_name,
aup.last_name,
aup.profileimage
FROM
useractivitylog ua
JOIN requestfriend rf
ON ua.userid IN (rf.userid, rf.friendid)
JOIN auth_user_profiles aup
ON aup.user_id = IF( rf.userid = '1', rf.friendid, rf.userid )
JOIN
(SELECT
activity_type,
activity_id,
MAX(`date`) max_date
FROM
useractivitylog WHERE userid != 1
GROUP BY activity_type,
activity_id) ua_new
ON (
ua.activity_type = ua_new.activity_type
AND ua.activity_id = ua_new.activity_id
AND ua.date = ua_new.max_date
)
WHERE (
rf.userid =1
OR rf.friendid =1
)
AND rf.status = 2
AND ua.userid != 1
ORDER BY ua.date DESC ;
Upvotes: 1
Related Questions
- Mysql group by multiple columns with different values
- MySQL GROUP BY multiple columns and fields
- MySQL: GROUP by with multiple columns
- MySQL - Group by multiple columns from same table
- php mysql group by
- How to use group by to get multiple result set? grouping on multiple columns
- SQL GROUP BY multiple fields
- MYSQL Groupby based on multiple fields
- mysql group by in php
- GROUP BY on multiple columns