SET @partner_num=(SELECT COUNT(*) FROM partner2);
PREPARE GET_PARTNERS_WITH_MIN_SORT FROM
'
SELECT par.*, per.*, ph.*
FROM person2 per
JOIN partner2 par ON per.partner_id = par.id
JOIN phone2 ph ON per.id = ph.person_id
GROUP BY par.title
ORDER BY ph.sort_phone DESC, per.sort, par.title
';
EXECUTE GET_PARTNERS_WITH_MIN_SORT USING @partner_num;

CREATE TABLE IF NOT EXISTS partner (id int PRIMARY KEY, title varchar(50));
CREATE TABLE IF NOT EXISTS person (id int PRIMARY KEY, partner_id int, title varchar(50), sort int);
CREATE TABLE IF NOT EXISTS phone (id int PRIMARY KEY, person_id int, value varchar(50), sort_phone int);

INSERT INTO partner VALUES
(1, 'Гугл инкорпорейтед'),
(2, 'Майкрософт'),
(3, 'Эпл компутерс');

INSERT INTO person VALUES
(1, 1, 'Сергей Брин', 1),
(2, 1, 'Ларри Пейдж', 2),
(3, 2, 'Билл Гейтс', 1),
(4, 2, 'Стив Балмер', 2),
(5, 3, 'Стив Джобс', 1),
(6, 3, 'Стив Возняк', 2);

INSERT INTO phone VALUES
(1, 1, '911', 1),
(2, 1, '102', 2),
(3, 2, '02', 5),
(4, 2, '03', 6),
(5, 3, '04', 10),
(6, 3, '09', 9),
(7, 4, '101', 7),
(8, 4, '111', 8),
(9, 5, '104', 3),
(10, 5, '112', 4),
(11, 6, '996', 11),
(12, 6, '454', 12);


SET @partner_num=(SELECT COUNT(*) FROM partner);
PREPARE GET_PARTNERS_WITH_MIN_SORT FROM
'
SELECT par.*, per.*, ph.*
FROM person per
JOIN partner par ON per.partner_id = par.id
JOIN phone ph ON per.id = ph.person_id
ORDER BY per.sort, ph.sort_phone, par.title
LIMIT ?;
';
EXECUTE GET_PARTNERS_WITH_MIN_SORT USING @partner_num;

DROP TABLE IF EXISTS partner;
DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS phone;