clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591537 fiddles created (45743 in the last week).

CREATE TABLE category ( `id` INTEGER, `type` VARCHAR(6), `subtype` VARCHAR(7), `pos` INTEGER ); INSERT INTO category (`id`, `type`, `subtype`, `pos`) VALUES ('1', 'sell', '', '1'), ('2', 'jobs', '', '2'), ('3', 'dating', '', '3'), ('4', 'dating', 'boys', '1'), ('5', 'dating', 'girls', '2'), ('5', 'sell', 'cars', '1'), ('5', 'jobs', 'teacher', '1'); CREATE TABLE ads ( `id` INTEGER, `title` VARCHAR(9), `type` VARCHAR(6), `subtype` VARCHAR(5) ); INSERT INTO ads (`id`, `title`, `type`, `subtype`) VALUES ('1', 'some text', 'sell', 'cars'), ('2', 'some text', 'dating', 'girls'), ('3', 'some text', 'dating', 'boys');
 hidden batch(es)


SELECT c.*,a.c_ads FROM category c LEFT JOIN ( SELECT COUNt(type) c_ads,type FROM ads GROUP BY type) a ON c.type = a.type ORDER BY c.type,c.pos DESC
id type subtype pos c_ads
3 dating 3 2
5 dating girls 2 2
4 dating boys 1 2
2 jobs 2
5 jobs teacher 1
1 sell 1 1
5 sell cars 1 1
 hidden batch(es)