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');
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?