2

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?

3

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.

  • 1
    The only disadvantage is that we can't create an index for sorting by function and thus results will be ordered by filesort every time. – Kondybas Jun 23 '17 at 9:44
  • @Kondybas: Yes, that can be a serious concern if performance is an issue. – Andriy M Jun 23 '17 at 9:47
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.