MySql :: Group by where date time max -

MySql :: Group by where date time max -

i have table result

idchat user_name text current_date_time chat_counterparty party 54 taruna reddish hat 2014-10-30 05:44:26 pooja pooja 55 pooja cat 2014-10-30 05:45:48 taruna pooja

after executing below query

select `idchat`, `user_name`, `text`, `current_date_time`, `chat_counterparty`, if(user_name='taruna',`chat_counterparty`,`user_name`) party ( select c.* chat c bring together ( select max(`current_date_time`) current_date_time, `chat_counterparty` chat grouping `chat_counterparty` ) c1 using (current_date_time,`chat_counterparty`) c.user_name='taruna' union select c.* chat c bring together ( select max(`current_date_time`) current_date_time,`user_name` chat grouping `user_name` ) c1 using (current_date_time,`user_name`) c.chat_counterparty='taruna' ) res

now want grouping party , want info max date time i.e. idchat 55. how can add together grouping in because current grouping giving me top row or other if result table large.

create table if not exists `chat` ( `idchat` int(11) not null auto_increment, `user_name` varchar(12) not null, `text` varchar(512) default null, `current_date_time` timestamp not null default current_timestamp, `chat_counterparty` varchar(12) not null, primary key (`idchat`), key `chat_chat_counterparty_idx` (`chat_counterparty`), key `chat_current_date_time_idx` (`current_date_time`) ) engine=innodb default charset=utf8 auto_increment=56 ; -- -- dumping info table `chat` -- insert `chat` (`idchat`, `user_name`, `text`, `current_date_time`, `chat_counterparty`) values (1, 'taruna', 'hello', '2014-10-30 11:57:11', 'pooja'), (2, 'pooja', 'hey', '2014-10-30 11:57:36', 'taruna'), (3, 'taruna', 'r u getting msg', '2014-10-30 11:58:07', 'pooja'), (4, 'pooja', 'yes', '2014-10-30 11:58:12', 'taruna'), (5, 'pooja', 'yes', '2014-10-30 11:58:42', 'taruna'), (6, 'pooja', 'u will', '2014-10-30 11:59:04', 'taruna'), (7, 'taruna', ':)', '2014-10-30 12:00:17', 'pooja'), (8, 'pooja', 'why u dont reply me', '2014-10-30 12:00:21', 'taruna'), (9, 'pooja', '?', '2014-10-30 12:00:25', 'taruna'), (10, 'taruna', 'i want purchase lappy', '2014-10-30 12:00:57', 'pooja'), (11, 'pooja', 'sure', '2014-10-30 12:01:19', 'taruna'), (12, 'taruna', 'fr hw much', '2014-10-30 12:01:34', 'pooja'), (13, 'pooja', '1 lakh', '2014-10-30 12:01:44', 'taruna'), (14, 'taruna', 'dimag khrb h???', '2014-10-30 12:02:23', 'pooja'), (15, 'taruna', 'der??', '2014-10-30 12:04:08', 'pooja'), (16, 'taruna', 'test', '2014-10-30 12:04:10', 'pooja'), (17, 'taruna', 'gv sm rply', '2014-10-30 12:11:18', 'pooja'), (18, 'pooja', 'test', '2014-10-30 12:11:32', 'taruna'), (19, 'taruna', '????', '2014-10-30 12:11:43', 'pooja'), (20, 'pooja', 'u got msgs', '2014-10-30 12:11:48', 'taruna'), (21, 'taruna', 'anything else??', '2014-10-30 12:11:50', 'pooja'), (22, 'pooja', '??', '2014-10-30 12:11:54', 'taruna'), (23, 'taruna', 'yupp', '2014-10-30 12:12:08', 'pooja'), (24, 'taruna', 'hie', '2014-10-30 12:12:22', 'pooja'), (25, 'taruna', 'hlo', '2014-10-30 12:12:30', 'pooja'), (26, 'pooja', 'hi', '2014-10-30 12:13:09', 'taruna'), (27, 'pooja', 'hi', '2014-10-30 12:13:16', 'taruna'), (28, 'pooja', 'taruna', '2014-10-30 12:13:33', 'taruna'), (29, 'taruna', 'hws u', '2014-10-30 12:13:35', 'pooja'), (30, 'taruna', 'ring nhi ho rha phn', '2014-10-30 12:14:36', 'pooja'), (31, 'taruna', 'jai ho', '2014-10-30 12:15:52', 'pooja'), (32, 'pooja', 'i find error', '2014-10-30 12:17:33', 'taruna'), (33, 'pooja', 'u got it', '2014-10-30 12:17:45', 'taruna'), (34, 'taruna', 'yupppp', '2014-10-30 12:17:59', 'pooja'), (35, 'pooja', 'r u well??', '2014-10-30 12:19:40', 'taruna'), (36, 'pooja', 'hey reply', '2014-10-30 12:23:07', 'taruna'), (37, 'pooja', 'taruna', '2014-10-30 12:23:11', 'taruna'), (38, 'pooja', 'hey', '2014-10-30 12:23:15', 'taruna'), (39, 'pooja', 'hey', '2014-10-30 12:23:17', 'taruna'), (40, 'pooja', 'hey', '2014-10-30 12:23:18', 'taruna'), (41, 'pooja', 'hey', '2014-10-30 12:23:19', 'taruna'), (42, 'pooja', 'hi', '2014-10-30 12:24:06', 'taruna'), (43, 'pooja', 'taru una', '2014-10-30 12:31:44', 'taruna'), (44, 'taruna', 'its taruna', '2014-10-30 12:32:53', 'pooja'), (45, 'taruna', 'nt taruuna', '2014-10-30 12:34:22', 'pooja'), (46, 'pooja', 'whos u', '2014-10-30 12:35:42', 'taruna'), (47, 'taruna', 'taruna sharma', '2014-10-30 12:36:05', 'pooja'), (48, 'pooja', 'hi', '2014-10-30 12:39:27', 'taruna'), (49, 'taruna', 'hlo', '2014-10-30 12:40:00', 'pooja'), (50, 'pooja', 'hi', '2014-10-30 12:40:56', 'taruna'), (51, 'taruna', '133', '2014-10-30 12:41:38', 'pooja'), (52, 'taruna', '1234', '2014-10-30 12:41:41', 'pooja'), (53, 'pooja', 'hat', '2014-10-30 12:42:14', 'taruna'), (54, 'taruna', 'red hat', '2014-10-30 12:44:26', 'pooja'), (55, 'pooja', 'cat', '2014-10-30 12:45:48', 'taruna');

i tested multiple convos, should good.

select * (select *, if(user_name='taruna',chat_counterparty,user_name)as `party` `chat` `user_name` = 'taruna' or `chat_counterparty` = 'taruna' order `current_date_time` desc) `parties` grouping `parties`.`party`

mysql group-by