0

We are working with some legacy code that is using a sub-optimally normalized DB. The problem that we are trying to solve is best described with an illustrative example.
hi_priority and lo_priority are two tables that have the same schema, as defined below:

CREATE TABLE hi_priority
(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(16),
  col1 VARCHAR(16),
  col2 INT,

  PRIMARY KEY(id)
);



CREATE TABLE lo_priority
(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(16),
  col1 VARCHAR(16),
  col2 INT,

  PRIMARY KEY(id)
);

In each table there is a 1:1 mapping between the name and col2.

The idea is to potentially SELECT name, col1, col2 from both tables but limit the total number of distinct names (let's say 7 for our illustrative example), subject to the following constraints:

  • Preference is to be given to entries from hi_priority table over entries from lo_priority tables.
  • For example, if there are 15 distinct names in hi_priority table, we will select all rows with 7 names from hi_priority table, ordered by col2.
  • If there are 5 distinct names in hi_priority table, we select all rows with those 5 names and on top of that we select all rows with 2 distinct names from lo_priority table, ordered by col2
  • If there are some entries in both the hi_priority table and the lo_priority table, we select entries only from the hi_priority table

Let us assume that the tables are populated as follows:

INSERT INTO hi_priority(name, col1, col2) VALUES('john', 'kl7y5tis4yh4', 1), ('john', 'tiid6iywq02k', 1), 
('john', 'detawgsxz615', 1), ('amy', 'i6u4hhc1trjk', 2), ('amy', 'wdpt0t5vtmbt', 2),
('amy', '87z5wgdfztwl', 2), ('amy', 'oj98jjdpb5yv', 2), ('steve', 'hllaazonflf0', 3),
('steve', '0h22y66kq3ow', 3), ('steve', 'o04ksti7di60', 3), ('steve', '3qlylbzqfr51', 3),
('steve', 'wmisshywtb12', 3), ('bob', 'ku8wpf7d6ta9', 3), ('bob', '6t7mn60g0g18', 3),
('bob', 'qv0s7ho3jku6', 3), ('bob', '5qgz7dznzwvn', 3), ('bob', 'mm76nhimm6fu', 3),
('bob', 'k7nlpksc55t2', 3), ('oliver', 'gvf9kjewpj7h', 4), ('oliver', '24w5s30w5te6', 4);


INSERT INTO lo_priority(name, col1, col2) VALUES('doug', 'j205tzrsfmax', 11), ('doug', '0w7rcazbh6es', 11),
('doug', '6xswf8frsjaw', 11), ('john', 'iw7d14vtysz2', 2), ('john', '6lg667dygaz1', 2),
('john', '83uk5dcobpu5', 2), ('john', 'tl8cpzatv0n9', 2), ('mike', '2dsarwozpmci', 3),
('mike', 'hinn6w03wdib', 3), ('mike', '4sxbgyacmjob', 3), ('mike', 'm4q13ln9gctj', 3),
('mike', 'pnip9c8cejo9', 3), ('steve', 'faff9p9v96x4', 4), ('steve', 'd5mxqpd3k8zi', 4),
('martha', 'bxggn5t6d2xn', 8), ('martha', 't05mi47i4n6l', 8), ('martha', 'p30wmw2o6nty', 8),
('martha', 'wip6efajt9yv', 8);

The expected output is:  
+--------+------------+------+
| name   | col1       | col2 |
+--------+------------+------+
| john   | kl7y5tis4y |    1 |
| john   | tiid6iywq0 |    1 |
| john   | detawgsxz6 |    1 |
| amy    | oj98jjdpb5 |    2 |
| amy    | 87z5wgdfzt |    2 |
| amy    | wdpt0t5vtm |    2 |
| amy    | i6u4hhc1tr |    2 |
| bob    | k7nlpksc55 |    3 |
| bob    | mm76nhimm6 |    3 |
| bob    | 5qgz7dznzw |    3 |
| bob    | qv0s7ho3jk |    3 |
| bob    | 6t7mn60g0g |    3 |
| bob    | ku8wpf7d6t |    3 |
| steve  | wmisshywtb |    3 |
| steve  | 3qlylbzqfr |    3 |
| steve  | o04ksti7di |    3 |
| steve  | 0h22y66kq3 |    3 |
| steve  | hllaazonfl |    3 |
| mike   | 4sxbgyacmj |    3 |
| mike   | pnip9c8cej |    3 |
| mike   | m4q13ln9gc |    3 |
| mike   | hinn6w03wd |    3 |
| mike   | 2dsarwozpm |    3 |
| oliver | gvf9kjewpj |    4 |
| oliver | 24w5s30w5t |    4 |
| martha | bxggn5t6d2 |    8 |
| martha | t05mi47i4n |    8 |
| martha | p30wmw2o6n |    8 |
| martha | wip6efajt9 |    8 |
+--------+------------+------+
  • Amy, Bob and Oliver are only present in hi_priority table and will be selected.
  • Mike and Martha are only present in lo_priority table and will be selected.
  • John and Steve are present in both hi_priority table and lo_priority table but will be selected only from hi_priority table
0
2
WITH
cte_hi AS ( SELECT DISTINCT name, hi_priority.col2, 1 priority 
            FROM hi_priority
            ORDER BY col2, name LIMIT 7 ),
cte_lo AS ( SELECT DISTINCT lo_priority.name, lo_priority.col2, 2 priority 
            FROM lo_priority
            LEFT JOIN cte_hi USING (name)
            WHERE cte_hi.name IS NULL
            ORDER BY col2, name LIMIT 7 ),
cte_total AS ( SELECT * FROM cte_hi
               UNION ALL
               SELECT * FROM cte_lo
               ORDER BY priority, col2 LIMIT 7 )
SELECT hi_priority.*
FROM cte_total
JOIN hi_priority USING (name)
WHERE priority = 1
UNION ALL
SELECT lo_priority.*
FROM cte_total
JOIN lo_priority USING (name)
WHERE priority = 2
ORDER BY col2, name, id

fiddle

2
  • The solution works really well on dfiddle. Can you kindly suggest a solution that would work on an older version of the DB that does not support CTEs? – Sandeep Feb 11 at 17:22
  • @Sandeep Replace WITH cte AS (xxxxx) SELECT ... FROM ... JOIN cte ... with SELECT ... FROM ... JOIN (xxxxx) cte ... with accuracy. Start replacing from the last cte_total, then replace cte_lo and finally replace cte_hi. – Akina Feb 11 at 17:36
0

The way to give preference to one part of a UNION is

( SELECT 1 AS priority, ... FROM hi_priority ORDER BY .. LIMIT 7 )
UNION ALL
( SELECT 2 AS priority, ... FROM lo_priority ORDER BY .. LIMIT 7 )
ORDER BY priority, ...   -- Note: start with `priority`, then rest of ordering
LIMIT 7

Note that ORDER BY .. LIMIT on each part of the UNION and another ORDER BY .. on the end of the UNION.

If you also have OFFSET, it gets a little more complex. I discuss it here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or

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.