Not the answer you need?
Register and ask your own question!

Mysql Sort by desc leaving NULL on last

ZeeeshanZeeeshan EntrantInactive User Role Beginner
I have two tables sms_contacts and sms_twoway_chat. Contact table use id as PK and contact_type is used for 3 psooible types of contacts (0,1 and 2)
sms_twoway_chat holds all chats between two contacts.

I need all contacts with contact_type 0, either they have chat or not
I need only contacts with contact_type 1 and 2 who has received replies
I need unread (is_read = 0) and recent response of chat (date_time desc ) on top

What I have done is showing Null on top, like contact of type 0 who don't have any record in chat table are displaying on top, whereas they should be on last


CREATE TABLE IF NOT EXISTS `sms_contacts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`contacts_name` varchar(50) DEFAULT NULL,
`contacts_phone` varchar(11) DEFAULT NULL,
`date_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`date_time_unix` bigint(20) DEFAULT NULL,
`contact_type` int(1) DEFAULT '0' COMMENT '0=>contact 1=>group , 2=>instant msg',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;


CREATE TABLE IF NOT EXISTS `sms_twoway_chat` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`inbound_id` varchar(50) DEFAULT NULL,
`sender_id` int(11) DEFAULT NULL,
`receiver_id` int(11) DEFAULT NULL,
`is_read` int(1) DEFAULT '0',
`msg_body` text,
`date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`date_time_unix` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=89 ;


Sample Data
--
-- Dumping data for table `sms_contacts`
--

INSERT INTO `sms_contacts` (`id`, `user_id`, `contacts_name`, `contacts_phone`, `date_time`, `date_time_unix`, `contact_type`) VALUES
(1, 1, 'Shane Higgins Cell', '16198671013', '2017-01-30 18:25:52', 1485800752, 0),
(2, 1, 'Arnetta Shepherd ssc hcm fb 1/24/17 [email protected]', '18572361426', '2017-01-31 17:38:14', 1485884294, 0),
(3, 1, 'Pierrot Mervilier ssc hcm fb 1/23', '13058965706', '2017-01-31 17:38:43', 1485884323, 0),
(4, 1, 'Sean Green ssc hcm 1/22 fb', '19039445724', '2017-01-31 17:39:10', 1485884350, 0),
(5, 1, 'Antonio Rodriguez fb ssc hcm 1/20', '19496624429', '2017-01-31 17:39:31', 1485884371, 0),
(6, 1, 'Diamond Newt fb ssc 1/31 [email protected]', '13368655338', '2017-01-31 17:44:56', 1485884696, 0),
(7, 1, 'Beatrice folston fb ssc hcm 1/31 beatricefolston00', '16015663527', '2017-01-31 17:47:24', 1485884844, 0),
(8, 1, 'KK Okechukwu fb ssc hcm 1/28 [email protected]', '16788870010', '2017-01-31 17:48:02', 1485884882, 0),
(9, 1, 'Crystal SSC RESELLER CL AD 2.1.17', '16194023797', '2017-02-01 04:10:28', 1485922228, 0),
(10, 1, 'Curtis SSC RESELLER CL AD 2.1.17', '16192731911', '2017-02-01 19:24:58', 1485977098, 0),
(12, 1, 'Keona Love ssc fb 2.1.17', '18162068951', '2017-02-01 20:12:05', 1485979925, 0),
(13, 1, '12132466310', '12132466310', '2017-02-01 20:15:08', 1485980108, 2),
(14, 1, '19518428547', '19518428547', '2017-02-01 20:15:55', 1485980155, 2),
(15, 1, 'test', '12341231111', '2017-02-03 22:15:51', 1486160151, 0),
(16, 1, 'test', '19087770009', '2017-02-03 22:16:20', 1486160180, 0),
(17, 1, '16196023730', '16196023730', '2017-02-06 17:51:51', 1486403511, 0),
(18, 1, '16195208189', '16195208189', '2017-02-06 17:56:34', 1486403794, 0);



--
-- Dumping data for table `sms_twoway_chat`
--

INSERT INTO `sms_twoway_chat` (`id`, `inbound_id`, `sender_id`, `receiver_id`, `is_read`, `msg_body`, `date_time`, `date_time_unix`) VALUES
(1, '0B0000002FAFA017', 1, 1, 1, 'Test', '2017-01-30 18:25:52', 1485800752),
(2, NULL, 1, 1, 1, 'Received', '2017-01-30 18:26:04', 1485800764),
(3, NULL, 1, 2, 0, 'Is this Arnetta?', '2017-01-31 17:49:36', 1485884976),
(4, NULL, 1, 3, 0, 'Is this Pierrot?', '2017-01-31 17:49:46', 1485884986),
(5, NULL, 1, 4, 0, 'Is this Sean?', '2017-01-31 17:49:53', 1485884993),
(6, NULL, 1, 5, 0, 'Is this Antonio?', '2017-01-31 17:50:00', 1485885000),
(7, NULL, 1, 6, 0, 'Is this Diamond? Or Monique?', '2017-01-31 17:50:10', 1485885010),
(8, NULL, 1, 7, 0, 'Is this Beatrice?', '2017-01-31 17:50:19', 1485885019),
(9, NULL, 1, 7, 0, 'Is this Benson ?', '2017-01-31 17:50:35', 1485885035),
(10, NULL, 1, 1, 1, 'is this shane?', '2017-01-31 17:50:43', 1485885043),
(11, '0C00000015510F1C', 2, 1, 1, 'Sorry, Whose This?', '2017-01-31 17:50:48', 1485885048),
(12, '0C000000155115ED', 7, 1, 1, 'Yes', '2017-01-31 17:51:13', 1485885073),
(13, '0C00000015511C47', 7, 1, 1, 'Yes it is', '2017-01-31 17:51:37', 1485885097),
(14, NULL, 1, 2, 0, 'Hi Arnetta, this is Shane. I own www.SmartSocialConnect.com You had entered your info from our Facebook ad so I wanted to follow up with you about it. Do you', '2017-01-31 17:51:46', 1485885106),
(15, NULL, 1, 2, 0, 'remember? The Facebook Messenger Service for your Facebook Business Page...\n', '2017-01-31 17:52:06', 1485885126),
(16, NULL, 1, 8, 0, 'Is this Benson?', '2017-01-31 17:52:59', 1485885179),
(17, NULL, 1, 7, 0, 'Hi Beatrice, I''m Shane. I own www.SmartSocialConnect.com You had entered your info from our Facebook ad so wanted to follow up w/ you about it. Do you recall?', '2017-01-31 17:53:54', 1485885234),
(18, '0B0000002FF2F974', 1, 1, 1, 'Yes', '2017-01-31 17:54:31', 1485885271),
(19, '0B0000002FF2FEC6', 7, 1, 1, 'I think so', '2017-01-31 17:54:57', 1485885297),
(20, NULL, 1, 7, 0, 'Yea sorry for the delay in getting back to you. We are super busy, this service is a no brainer especially if you are paying to advertise on FB. ', '2017-01-31 17:56:01', 1485885361),
(21, '0C000000155165ED', 7, 1, 1, 'What about', '2017-01-31 17:56:04', 1485885364),
(22, NULL, 1, 7, 0, 'Is there a good time to give you a call? Id love to explain in more detail to you about this brand new service that is guaranteed to boost your sales. ', '2017-01-31 17:56:42', 1485885402),
(23, '0C00000015517D8E', 7, 1, 1, 'Tomorrow evening at 5', '2017-01-31 17:57:38', 1485885458),
(24, '0C0000001551808B', 7, 1, 1, 'I am at work now', '2017-01-31 17:57:50', 1485885470),
(25, NULL, 1, 7, 0, 'ok sounds great. I will call you then. Have a great rest of your day and sorry for bugging in the middle of your work day. ', '2017-01-31 17:59:23', 1485885563),
(26, '0B0000002FF3405D', 7, 1, 1, 'That's OK be bless', '2017-01-31 17:59:59', 1485885599),
(27, '0B0000002FF38896', 4, 1, 1, 'Yes', '2017-01-31 18:04:47', 1485885887),
(28, '0C0000001551F6AC', 4, 1, 1, 'Whi this', '2017-01-31 18:04:54', 1485885894),
(29, NULL, 1, 4, 0, 'Hi Sean I''m Shane I own www.SmartSocialConnect.com You had entered your info from our Facebook ad so wanted to follow up w/ you about it. Do you recall?', '2017-01-31 18:06:32', 1485885992),
(30, NULL, 1, 4, 0, 'Were the company that sends messages through facebook messenger on your behalf to everyone that likes, shares, comments or likes your pages paid or unpaid...', '2017-01-31 18:07:10', 1485886030),
(31, NULL, 1, 4, 0, 'Is there a good time to give you a call? Id love to explain in more detail to you about this brand new service that is guaranteed to boost your sales. ', '2017-01-31 18:08:27', 1485886107),
(32, NULL, 1, 4, 0, 'Its been a week, sorry for the delay in getting back to you but im literally signing up a hundred businesses a week all by myself so just getting caught up', '2017-01-31 18:10:41', 1485886241),
(33, NULL, 1, 4, 0, 'LMK if you are still interested :)', '2017-01-31 18:10:53', 1485886253),
(34, '0C000000155296A9', 6, 1, 1, 'Who is this?\n🇯🇲MoneyK'aSh🇯🇲', '2017-01-31 18:11:10', 1485886270),
(35, NULL, 1, 6, 0, 'I''m Shane I own www.SmartSocialConnect.com You had entered your info from our Facebook ad so wanted to follow up w/ you about it. ', '2017-01-31 18:11:51', 1485886311),
(36, NULL, 1, 6, 0, 'Were the company that sends messages through facebook messenger on your behalf to everyone that likes, shares, comments or likes your pages/posts.. do u recall?', '2017-01-31 18:12:36', 1485886356),
(37, NULL, 1, 1, 1, 'test', '2017-01-31 19:33:34', 1485891214),
(38, '0C0000001557D773', 1, 1, 1, 'Got it', '2017-01-31 19:34:13', 1485891253),
(39, '0B00000030142263', 9, 1, 1, 'Hi Shane, my name is crystal. I saw your ad on Craigslist and I think its awesome and I was interested in hearing more about the details', '2017-02-01 04:10:28', 1485922228),
(88, '0C00000016FF7C57', 18, 1, 0, 'Test', '2017-02-06 17:56:34', 1486403794);


This is what I have tried
SELECT a.*
FROM ( SELECT c.id,c.`contacts_name` FROM sms_contacts c
LEFT JOIN sms_twoway_chat tc ON c.id = tc.`receiver_id`
WHERE user_id = 1 AND c.contact_type = 0
ORDER BY -tc.`is_read`, tc.date_time DESC
) a
UNION
SELECT b.*
FROM ( SELECT c.id,c.`contacts_name` FROM sms_contacts c
INNER JOIN sms_twoway_chat tc ON c.id = tc.`receiver_id`
WHERE user_id = 1 AND c.contact_type IN(1,2)
ORDER BY -tc.`is_read`,tc.date_time DESC
) b;
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.