Query with custom sorting 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?
The easiest way is to hold reference to itself in the duplicate_with for the master records while their is_duplicate fields contains zero. Then all the group (master and all its slaves) can be extracted by duplicate_with field value:

SELECT * 
  FROM sample
 ORDER BY duplicate_with ASC
        , is_duplicate DESC
        , id ASC
;


If you can't for some reason update master records then you can do the same by joining table with itself:

SELECT z.id AS id
     , z.is_duplicate AS is_duplicate
     , z.duplicate_with AS duplicate_with
     , z.name AS name
  FROM sample AS w
  JOIN sample AS z ON z.duplicate_with = w.id
                   OR z.id = w.id
 ORDER BY w.id ASC
        , z.is_duplicate DESC
        , z.id ASC
;


Sure that approach can be significantly slower on the big tables.