ORDER by multiple queries in MySql

Question

I am trying to build a chat list page where latest sent/received contact is shown at the top from one table. For this, I have a table smshistory where i store sent/received sms with numbers where one is company phone and other is client phone number

CREATE TABLE IF NOT EXISTS `smshistory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fromnumber` varchar(20) NOT NULL,
  `tonumber` varchar(20) NOT NULL,
  `sms` varchar(20) NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `smshistory`
--

INSERT INTO `smshistory` (`id`, `fromnumber`, `tonumber`, `sms`, `added`) VALUES
(1, 'companynum', 'client1num', 'Hello', '2021-07-16 12:28:23'),
(2, 'companynum', 'client2num', 'Hello', '2021-07-16 12:28:23'),
(3, 'companynum', 'client3num', 'Hello', '2021-07-16 12:28:23'),
(4, 'client1num', 'companynum', 'Hi', '2021-07-16 12:28:23'),
(5, 'companynum', 'client1num', 'Hello', '2021-07-16 12:28:23'),
(6, 'client1num', 'companynum', 'Hi', '2021-07-16 12:28:23'),
(7, 'client2num', 'companynum', 'Hi', '2021-07-16 12:28:23'),
(8, 'companynum', 'client2num', 'Hello', '2021-07-16 12:28:23'),
(9, 'client3num', 'companynum', 'Hi', '2021-07-16 12:28:23');

As first message will always be from company number, so I am showing DISTINCT list with:

SELECT DISTINCT (`tonumber`) FROM `smshistory` WHERE `fromnumber` = $companynum

Which gives me list like:

client1num
client2num
client3num

Requirement:

What I require is to show the DISTINCT with order of added DESC column in a way that if a client's number is in fromnumber or tonumber, it should show at top. So, according to my table, results should be:

client3num
client2num
client1num

Fiddle is at http://sqlfiddle.com/#!9/4256d1d/1

Any idea on how to achieve that?

Answer 1

For each of the rows that contain $companynum either in fromnumber or in tonumber you must extract the client's number with a CASE expression and use GROUP BY to remove duplicates.
Finally, sort the results by the max value of added:

SELECT CASE WHEN fromnumber = $companynum THEN tonumber ELSE fromnumber END client_num
FROM smshistory
WHERE $companynum IN (fromnumber, tonumber)
GROUP BY client_num
ORDER BY MAX(added) DESC