MySQL – How to Perform Custom Sorting in Queries

MySQLorder-by

I have a table structure like below:

CREATE TABLE `sample` (
  `id` int(11) NOT NULL,
  `is_duplicate` tinyint(1) NOT NULL DEFAULT '0',
  `duplicate_with` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO `sample` (`id`, `is_duplicate`, `duplicate_with`,`name`) VALUES
(1, 0, 0,'ABC'),
(2, 0, 0,'PQR'),
(3, 1, 1,'XYZ'),
(4, 1, 2,'PPP');

The is_duplicate column is an indicator of whether the row is a duplicate of another row (1) or is a master row (0). If it is a duplicate, the duplicate_with column will hold the ID of the master row of which this one is a duplicate.

Now I want output like below: 

3  1  1  XYZ
1  0  0  ABC
4  1  2  PPP
2  0  0  PQR

I want to list rows in such a way that duplicates of the same master row are arranged together and followed by their master row, while the groups themselves should be sorted by master row ID.

In the above example, there are two row groups. The first group includes row 3 and its master row, row 1, which goes after its duplicate. In the same way, the second group consists of rows 4 and 2, row 2 being the master row and following its duplicate.

How can I achieve such a result?

Best Answer

You can use a CASE expression or an IF() expression to calculate the master ID to use it as the primary sorting criterion. The expression will check whether the row is a duplicate or not. If it is a duplicate, return duplicate_with, otherwise return id. In standard SQL it will look like this:

CASE is_duplicate WHEN 1 THEN duplicate_with ELSE id END

Using the not so standard IF() function, you can implement the same logic like this:

IF(is_duplicate = 1, duplicate_with, id)

or even simply:

IF(is_duplicate, duplicate_with, id)

Within each group, use is_duplicate DESC to put the master row after all its duplicates, and then id ASC to determine the order of the duplicate rows (assuming there can be more than one duplicate of the same master row). Those last two criteria are the same as in Kondybas's answer, by the way.

The entire ORDER BY clause, therefore, would look like this:

ORDER BY
  IF(is_duplicate, duplicate_with, id) ASC,
  is_duplicate DESC,
  id ASC

A demo of this approach can be found and played with at SQLFiddle, dbfiddle and Rextester.

Related Question