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. 2591646 fiddles created (45704 in the last week).

CREATE TABLE `languages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code_3` char(3) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `languages_code_3_uindex` (`code_3`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 hidden batch(es)


CREATE TABLE `countries` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 hidden batch(es)


CREATE TABLE `admins` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 hidden batch(es)


CREATE TABLE `places` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `position` point NOT NULL COMMENT 'Coordinates of the city.', `admin_id` bigint(20) DEFAULT NULL, `country_id` bigint(20) DEFAULT NULL, `population` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `admin_id` (`admin_id`), KEY `country_id` (`country_id`), SPATIAL KEY `position` (`position`), CONSTRAINT `places_admin_id_fk` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `places_countries_id_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 hidden batch(es)


CREATE TABLE `place_names` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `place_id` bigint(20) NOT NULL COMMENT 'ID of place in table places.', `name` char(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the place in the given language.', `language_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `place_names_place_id_language_uindex` (`place_id`,`language_id`), KEY `new_place_names_language_id_fk` (`language_id`), KEY `place_id` (`place_id`), KEY `place_name` (`name`), FULLTEXT KEY `place_name_fulltext` (`name`), CONSTRAINT `new_place_names_language_id_fk` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `new_place_names_places_id_fk` FOREIGN KEY (`place_id`) REFERENCES `places` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 hidden batch(es)


CREATE TABLE `country_names` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `country_id` bigint(20) NOT NULL COMMENT 'ID of country in table countries.', `language_id` int(11) NOT NULL, `name` char(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the country in the given language.', PRIMARY KEY (`id`), UNIQUE KEY `admin_names_country_id_language_uindex` (`country_id`,`language_id`), KEY `country_id` (`country_id`), KEY `language_id` (`language_id`), KEY `country_name` (`name`), FULLTEXT KEY `country_name_fulltext` (`name`), CONSTRAINT `country_names_countries_id_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `country_names_languages_id_fk` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2533 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 hidden batch(es)


CREATE TABLE `admin_names` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `admin_id` bigint(20) NOT NULL COMMENT 'ID of admin in table admins.', `language_id` int(11) NOT NULL, `name` char(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the administration in the given language.', `abbr` char(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Abbreviation of the administration, if available.', PRIMARY KEY (`id`), UNIQUE KEY `admin_names_admin_id_language_uindex` (`admin_id`,`language_id`), KEY `admin_id` (`admin_id`), KEY `language_id` (`language_id`), FULLTEXT KEY `admin_name_fulltext` (`name`), FULLTEXT KEY `admin_abbr_fulltext` (`abbr`), CONSTRAINT `admin_names_admins_id_fk` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `admin_names_languages_id_fk` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 hidden batch(es)


-- Initial contents INSERT INTO languages (id, code_3) VALUES (2, 'DEU'), (1, 'ENG'), (3, 'FRA'), (6, 'ITA'), (5, 'POR'), (4, 'SPA'); -- Example data (for some of the records that should appear in the query as written above) INSERT INTO admins (id) VALUES (35), (133), (9980), (61), (30113), (51), (128), (129), (130), (125), (120), (121), (123), (126), (132), (1476), (1280), (1032), (5740), (1504), (40), (41), (39), (325), (10), (5576), (1924), (24), (59), (2201), (696959); INSERT INTO countries (id) VALUES (4), (132), (342), (59), (1209), (856), (16), (10), (82), (24), (136); INSERT INTO country_names (id, country_id, language_id, name) VALUES (8, 4, 1, 'Germany'), (242, 132, 1, 'Austria'), (548, 342, 1, 'Iran'), (134, 59, 1, 'Netherlands'), (1094, 1209, 1, 'Hungary'), (926, 856, 1, 'Romania'), (44, 16, 1, 'Italy'), (20, 10, 1, 'United Kingdom'), (152, 82, 1, 'India'), (74, 24, 1, 'France'), (254, 136, 1, 'Sweden'); INSERT INTO places (id, position, admin_id, country_id, population) VALUES (88, 0xE6100000010100000013150FC771DC1D4070DE6F1CC7C14940, 35, 4, 587010), (1966, 0xE610000001010000000000000000802340D4E6535555B54740, 133, 132, 48067), (4856, 0xE610000001010000001374A9814EDB1B4014AE47E17AD44940, 35, 4, 74736), (10073, 0xE610000001010000009A999999998948407FA1F18B25BF4040, 9980, 342, 121608), (10321, 0xE610000001010000005C8FC2F5285C1540CDCCCCCCCCFC4940, 61, 59, 10000), (30643, 0xE61000000101000000A9CDA7AAAABA32406B226BC116DC4740, 30113, 1209, 11883), (39373, 0xE610000001010000005C8FC2F5285C1B40CEAACFD5568C4940, 35, 4, 64335), (39853, 0xE61000000101000000858CB7FD41062140DC5F7598BA2C4840, 51, 4, 6006), (39977, 0xE610000001010000003D4CA22EB7FF20405C8FC2F5283C4840, 51, 4, 8061), (40146, 0xE61000000101000000CDCCCCCCCC4C2840A1B3202222224840, 128, 4, 14650), (46153, 0xE610000001010000000AD7A3703D6A2740F6C9C1D4E5864940, 129, 4, 5426), (69341, 0xE610000001010000001E681C2222222B4037C0767777774940, 130, 4, 1093), (69544, 0xE610000001010000002663ECEC7ADB1F40BABCC706838B4840, 125, 4, 905), (69881, 0xE61000000101000000E15D457EB1E42A40E9949B36D0614940, 130, 4, 1551), (74577, 0xE61000000101000000DC5F7598BAFC2540D3B7923EE9234940, 128, 4, 3591), (75606, 0xE6100000010100000000000000000022408BD6172A3B044A40, 35, 4, 7720), (79421, 0xE61000000101000000DA1B7C6132F523409E5E29CB104F4B40, 120, 4, 733), (79443, 0xE610000001010000003E7958A835ED26406B2BF697DDEB4A40, 121, 4, 3090), (80042, 0xE610000001010000004267414444C42240048D434444E44840, 128, 4, 1755), (80439, 0xE61000000101000000B801B6BBBB3B2640CDCCCCCCCCCC4840, 128, 4, 2098), (84333, 0xE61000000101000000759A747777772240CDCCCCCCCC6C4A40, 123, 4, 8972), (89196, 0xE61000000101000000EBF21D64A88C21403DAB3CE9931E4840, 51, 4, 1128), (89531, 0xE610000001010000006666666666662340D159101111B14840, 51, 4, 2485), (89940, 0xE610000001010000009A99999999992240A1B3202222224B40, 120, 4, 624), (90042, 0xE610000001010000005BB4BE50D9E11E40E0ECB8DCFEF84840, 125, 4, 696), (94578, 0xE610000001010000000042CB8A46021F40AEE846C037CF4840, 125, 4, 147), (94606, 0xE61000000101000000A3369FAAAA2A1B4080D0B2A291304940, 125, 4, 190), (94615, 0xE61000000101000000612EF8204305204048E17A14AE1F4940, 125, 4, 423), (94641, 0xE6100000010100000066A831F1AC281F40E3A9D5C4B3224940, 125, 4, 197), (94836, 0xE610000001010000004782E059D1081F4005BC045BB00D4940, 125, 4, 382), (95134, 0xE61000000101000000A3369FAAAAAA1D403333333333D34A40, 123, 4, 4442), (95167, 0xE610000001010000007AF7DE174B7E1F401DF78F806FF64840, 125, 4, 718), (95544, 0xE610000001010000004782E059D1881F40C9E0EECDAB294940, 125, 4, 465), (99494, 0xE610000001010000000A196FFB830C2040D8D2315476404940, 126, 4, 8434), (99870, 0xE61000000101000000517E8025BF581D403B4DBABBBB7B4A40, 123, 4, 5287), (103271, 0xE6100000010100000070C1A0EC30552940F49A00BE79654740, 132, 132, 63), (103785, 0xE610000001010000001E681C2222E22340F36B3FA70D3C4840, 51, 4, 8686), (103867, 0xE610000001010000001E681C2222222540CDCCCCCCCC0C4A40, 123, 4, 705), (103952, 0xE61000000101000000B801B6BBBBBB2440D4E6535555154B40, 120, 4, 258), (104057, 0xE61000000101000000997CCA6903FD25407B14AE47E16A4940, 129, 4, 579), (108810, 0xE610000001010000004723469FF4892040B2D424BF58E24840, 125, 4, 999), (109447, 0xE610000001010000005C8FC2F5285C1E40525984CA0E134940, 125, 4, 520), (110365, 0xE610000001010000003333333333B32240406859D148384B40, 120, 4, 556), (123954, 0xE61000000101000000FB85C62F967C30401CC8CE6903BD4740, 1476, 132, 1291), (128696, 0xE61000000101000000941F60C92F362A406E80EDEEEE9E4740, 1280, 132, 1614), (128724, 0xE61000000101000000BDBDBA166C412B40A2E2E1388E234840, 1032, 132, 1039), (129077, 0xE6100000010100000076F90E3254663A40035E822DD8FA4740, 5740, 856, 24309), (133664, 0xE6100000010100000014F0126CC1F62D40BA4D4602BE294840, 1504, 132, 584), (134228, 0xE6100000010100000085CE828888082A40A1B3202222024840, 1280, 132, 1547), (135334, 0xE610000001010000000042CB8A46C223408E349A5713BF4740, 133, 132, 1029), (233062, 0xE61000000101000000C16ED8B628DB2240DD9733DB15D04840, 51, 4, 62), (238213, 0xE6100000010100000070036C7777771F409D26DDDDDDBD4640, 40, 16, 3430), (243102, 0xE6100000010100000085CE828888881D406E80EDEEEE8E4640, 40, 16, 2892), (243184, 0xE61000000101000000B3E616A62EB72B4048E17A14AECF4640, 41, 16, 5754), (243488, 0xE61000000101000000A9CDA7AAAA2A20403333333333B34640, 40, 16, 520), (243959, 0xE610000001010000001E681C22222221409D26DDDDDDDD4640, 40, 16, 2600), (248807, 0xE610000001010000006666666666E621403333333333934640, 39, 16, 4671), (253055, 0xE610000001010000001E681C2222A22240CDCCCCCCCC0C4740, 39, 16, 319), (281738, 0xE610000001010000008B6CE7FBA9D1254052B81E85EB094740, 325, 16, 1562), (283011, 0xE61000000101000000FF8396158D8403C022122F96FC5A4940, 10, 10, 20135), (403030, 0xE61000000101000000BA7BF042894C3140122DB1E68BCC4740, 5576, 1209, 675), (439462, 0xE61000000101000000F6285C8FC2C55240D7A3703D0AB74040, 1924, 82, 199429), (696911, 0xE6100000010100000032582F8EE338184065D80A951D864840, 24, 24, 169), (697194, 0xE610000001010000005A1B8636D069FD3F2DE9D2063A7D4640, 24, 24, 1135), (707266, 0xE61000000101000000BF8919C850D9F1BF7D723075B99D4740, 24, 24, 1182), (884374, 0xE61000000101000000AE47E17A14AE124048E17A14AEE74940, 59, 59, 118654), (1160830, 0xE61000000101000000B6A0D1FF6D012C40E28FA2CE5C7E4C40, 2201, 136, 129), (1306022, 0xE61000000101000000AE2A124B7EF11D403333333333434840, 24, 24, 2633), (1311153, 0xE61000000101000000237C436F5E4DF13FA2E2E1388E1B4740, 24, 24, 1638), (1315478, 0xE610000001010000008103D9396DA0E93F65D80A951D364840, 696959, 24, 386), (1320476, 0xE610000001010000000042CB8A46020E4058A3E4F607B14640, 24, 24, 645), (1321355, 0xE610000001010000005B552496FCA216404782E059D1284740, 24, 24, 1854), (1325957, 0xE6100000010100000052B81E85EBD10A40EC51B81E855B4740, 24, 24, 1448), (1330302, 0xE61000000101000000993AFFDEBC5A1B40EAC35C4D3CCB4740, 24, 24, 774), (1331044, 0xE610000001010000006376C0BC9A78064070DE6F1CC7C94640, 24, 24, 1280), (1331360, 0xE6100000010100000089E2A9D5C4B3DABF81FF73B9FDF14740, 24, 24, 352), (1335169, 0xE61000000101000000D7E53BC850D90B400AD7A3703DF24640, 24, 24, 701), (1335243, 0xE61000000101000000D471FF08F8660D4085EB51B81EFD4840, 24, 24, 379), (1335253, 0xE61000000101000000652CC8063AED0C406B226BC116B44640, 24, 24, 167), (1345066, 0xE61000000101000000610284FD4186FE3F59D2A50D748A4640, 24, 24, 638); INSERT INTO place_names (id, place_id, name, language_id) VALUES (1311224, 88, 'Dortmund', 1), (1322473, 1966, 'Dornbirn', 1), (1339814, 4856, 'Dorsten', 1), (1371119, 10073, 'Dorud', 1), (1372603, 10321, 'Dorestad', 1), (1494538, 30643, 'Dorog', 1), (1546916, 39373, 'Dormagen', 1), (1549800, 39853, 'Dornhan', 1), (1550544, 39977, 'Dornstetten', 1), (1551553, 40146, 'Dorfen', 1), (1587597, 46153, 'Dornburg-Camburg', 1), (1726728, 69341, 'Dorfhain', 1), (1727946, 69544, 'Dörrenbach', 1), (1729968, 69881, 'Dorfchemnitz', 1), (1758139, 74577, 'Dörfles-Esbach', 1), (1764314, 75606, 'Dörentrup', 1), (1787208, 79421, 'Dörphof', 1), (1787339, 79443, 'Dorf Mecklenburg', 1), (1790929, 80042, 'Dorfprozelten', 1), (1793311, 80439, 'Dormitz', 1), (1816679, 84333, 'Dörverden', 1), (1845858, 89196, 'Dormettingen', 1), (1847868, 89531, 'Dörzbach', 1), (1850322, 89940, 'Dörpling', 1), (1850934, 90042, 'Dörrebach', 1), (1878150, 94578, 'Dörrmoschel', 1), (1878318, 94606, 'Dorsel', 1), (1878372, 94615, 'Dörsdorf', 1), (1878528, 94641, 'Dornholzhausen', 1), (1879698, 94836, 'Dörscheid', 1), (1881485, 95134, 'Dornum', 1), (1881684, 95167, 'Dorsheim', 1), (1883946, 95544, 'Dörnberg', 1), (1907645, 99494, 'Dornburg', 1), (1909901, 99870, 'Dörpen', 1), (1930307, 103271, 'Dörfl', 1), (1933392, 103785, 'Dornstadt', 1), (1933883, 103867, 'Dorstadt', 1), (1934394, 103952, 'Dörnick', 1), (1935021, 104057, 'Dornheim', 1), (1963542, 108810, 'Dorn-Dürkheim', 1), (1967364, 109447, 'Dörth', 1), (1972872, 110365, 'Dörpstedt', 1), (2054401, 123954, 'Steinberg-Dörfl', 1), (2082855, 128696, 'Dorfgastein', 1), (2083026, 128724, 'Dorf an der Pram', 1), (2085142, 129077, 'Dorohoi', 1), (2112665, 133664, 'Dorfstetten', 1), (2116047, 134228, 'Dorfbeuern', 1), (2122681, 135334, 'Doren', 1), (2709054, 233062, 'Dornberg', 1), (2739956, 238213, 'Montalto Dora', 1), (2769290, 243102, 'Villar Dora', 1), (2769786, 243184, 'San Dorligo della Valle', 1), (2771606, 243488, 'Dorzano', 1), (2774432, 243959, 'Dormelletto', 1), (2803524, 248807, 'Dorno', 1), (2829012, 253055, 'Dorio', 1), (3001079, 281738, 'San Lorenzo Dorsino', 1), (3008714, 283011, 'Dorchester', 1), (3728824, 403030, 'Dör', 1), (3947413, 439462, 'Doru Shahabad', 1), (5492111, 696911, 'Dornot', 1), (5493809, 697194, 'Beaulieu-sur-Dordogne', 1), (5554241, 707266, 'Le Puiset-Doré', 1), (6616867, 884374, 'Dordrecht', 1), (8260207, 1160830, 'Dörarp', 1), (9131352, 1306022, 'Dorlisheim', 1), (9162138, 1311153, 'Le Dorat', 1), (9188085, 1315478, 'Dorceau', 1), (9218075, 1320476, 'Dore-l''''Église', 1), (9223350, 1321355, 'Dortan', 1), (9250962, 1325957, 'Dornes', 1), (9277032, 1330302, 'Dorans', 1), (9281484, 1331044, 'Mont-Dore', 1), (9283380, 1331360, 'Saint-Loup-du-Dorat', 1), (9306234, 1335169, 'Dorat', 1), (9306678, 1335243, 'La Neuville-lès-Dorengt', 1), (9306738, 1335253, 'Doranges', 1), (9365616, 1345066, 'Monceaux-sur-Dordogne', 1); INSERT INTO admin_names (id, admin_id, language_id, name, abbr) VALUES (194, 35, 1, 'North Rhine-Westphalia', 'NRW'), (656, 133, 1, 'Vorarlberg', NULL), (12056, 9980, 1, 'Lorestan Province', NULL), (296, 61, 1, 'Netherlands', NULL), (22142, 30113, 1, 'Komárom-Esztergom County', NULL), (272, 51, 1, 'Baden-Württemberg', 'BW'), (626, 128, 1, 'Bavaria', 'BY'), (632, 129, 1, 'Thuringia', 'TH'), (638, 130, 1, 'Saxony', 'SN'), (608, 125, 1, 'Rhineland-Palatinate', 'RP'), (584, 120, 1, 'Schleswig-Holstein', 'SH'), (590, 121, 1, 'Mecklenburg-Western Pomerania', 'MV'), (602, 123, 1, 'Lower Saxony', 'NDS'), (614, 126, 1, 'Hesse', 'HE'), (650, 132, 1, 'Tyrol', NULL), (4652, 1476, 1, 'Burgenland', NULL), (4334, 1280, 1, 'Salzburg', NULL), (3584, 1032, 1, 'Upper Austria', NULL), (8492, 5740, 1, 'Botoșani County', NULL), (4760, 1504, 1, 'Lower Austria', NULL), (218, 40, 1, 'Piedmont', NULL), (224, 41, 1, 'Friuli-Venezia Giulia', NULL), (212, 39, 1, 'Lombardy', NULL), (1334, 325, 1, 'Trentino-South Tyrol', NULL), (50, 10, 1, 'England', NULL), (8192, 5576, 1, 'Győr-Moson-Sopron County', NULL), (5624, 1924, 1, 'Jammu and Kashmir', NULL), (134, 24, 1, 'Metropolitan France', NULL), (290, 59, 1, 'South Holland', NULL), (6284, 2201, 1, 'Östergötland County', NULL), (225554, 696959, 1, 'Orne', NULL);
 hidden batch(es)


CREATE FUNCTION `rescale`(val double, min double, max double) RETURNS double NO SQL DETERMINISTIC RETURN (val - min) / IF((max - min) = 0, 0.00000001, (max - min))
 hidden batch(es)


SELECT MIN(place_id) AS place_id, name, administration, country, MAX(relevance) AS relevance FROM ( SELECT *, (rescale(population, mn_pop, mx_pop) * 2.4) + (rescale(name_relevance, mn_plre, mx_plre) * 0.0) + (rescale(distance, mx_dist, mn_dist) * 2.2) + -- inverted if(t.country_id = ( SELECT country_id FROM country_names WHERE name = 'Germany' LIMIT 1 ), 0.6, 0) / 4 AS relevance FROM ( SELECT resl.*, aggr.mn_pop, aggr.mx_pop, aggr.mn_plre, aggr.mx_plre, aggr.mn_dist, aggr.mx_dist FROM ( SELECT MIN(population) AS mn_pop, MAX(population) AS mx_pop, MIN(name_relevance) AS mn_plre, MAX(name_relevance) AS mx_plre, MIN(distance) AS mn_dist, MAX(distance) AS mx_dist FROM ( SELECT p.population, ST_DISTANCE_SPHERE(position, ST_POINTFROMTEXT(ST_ASTEXT(POINT(7.4653, 51.5136)), 4326)) AS distance, MATCH(pn.name) AGAINST('+dor*' IN BOOLEAN MODE) AS name_relevance FROM places p JOIN place_names pn ON p.id = pn.place_id JOIN admin_names an ON p.admin_id = an.admin_id JOIN country_names cn ON p.country_id = cn.country_id JOIN languages l ON pn.language_id = l.id AND an.language_id = l.id AND cn.language_id = l.id WHERE l.code_3 = 'ENG' AND MATCH(pn.name) AGAINST('+dor*' IN BOOLEAN MODE) LIMIT 200 ) AS s ) aggr JOIN ( SELECT p.id AS place_id, pn.name AS name, an.name AS administration, an.abbr AS admin_abbr, cn.name AS country, p.population AS population, p.country_id AS country_id, ST_DISTANCE_SPHERE(position, ST_POINTFROMTEXT(ST_ASTEXT(POINT(7.4653, 51.5136)), 4326)) AS distance, MATCH(pn.name) AGAINST('+rom*' IN BOOLEAN MODE) AS name_relevance FROM places p JOIN place_names pn ON p.id = pn.place_id JOIN admin_names an ON p.admin_id = an.admin_id JOIN country_names cn ON p.country_id = cn.country_id JOIN languages l ON pn.language_id = l.id AND an.language_id = l.id AND cn.language_id = l.id WHERE l.code_3 = 'ENG' AND MATCH(pn.name) AGAINST('+dor*' IN BOOLEAN MODE) LIMIT 200 ) resl /*GROUP BY place_id, resl.name, resl.administration, resl.admin_abbr, resl.country*/ ) t ) t2 WHERE place_id is not null GROUP BY country, administration, admin_abbr, name ORDER BY relevance DESC; /*SELECT 'Nothing to see' AS Result;*/
place_id name administration country relevance
88 Dortmund North Rhine-Westphalia Germany 4.75
4856 Dorsten North Rhine-Westphalia Germany 2.640516449078394
884374 Dordrecht South Holland Netherlands 2.6090928904140167
39373 Dormagen North Rhine-Westphalia Germany 2.5881720187400035
99494 Dornburg Hesse Germany 2.338134621149714
94606 Dorsel Rhineland-Palatinate Germany 2.2983267901815947
94641 Dornholzhausen Rhineland-Palatinate Germany 2.2962742258598188
95167 Dorsheim Rhineland-Palatinate Germany 2.283316313432495
95134 Dornum Lower Saxony Germany 2.2758044350780002
108810 Dorn-Dürkheim Rhineland-Palatinate Germany 2.2754531397718205
103867 Dorstadt Lower Saxony Germany 2.2661940305217247
80042 Dorfprozelten Bavaria Germany 2.265759117803767
46153 Dornburg-Camburg Thuringia Germany 2.255862347640068
233062 Dornberg Baden-Württemberg Germany 2.252480444722938
104057 Dornheim Thuringia Germany 2.2523651666407254
39977 Dornstetten Baden-Württemberg Germany 2.248407667817241
103785 Dornstadt Baden-Württemberg Germany 2.237174794885717
39853 Dornhan Baden-Württemberg Germany 2.2348419894501803
80439 Dormitz Bavaria Germany 2.2287130154767865
40146 Dorfen Bavaria Germany 2.218612630280063
79443 Dorf Mecklenburg Mecklenburg-Western Pomerania Germany 2.217503908971709
89196 Dormettingen Baden-Württemberg Germany 2.208579688893474
1966 Dornbirn Vorarlberg Austria 2.2084484866212333
69881 Dorfchemnitz Saxony Germany 2.191152942404659
69341 Dorfhain Saxony Germany 2.187643205565667
10321 Dorestad Netherlands Netherlands 2.1805298573744594
696911 Dornot Metropolitan France France 2.087271394678392
1306022 Dorlisheim Metropolitan France France 2.081673298476502
1335243 La Neuville-lès-Dorengt Metropolitan France France 2.0785449547806865
1330302 Dorans Metropolitan France France 2.032762287978025
135334 Doren Vorarlberg Austria 2.017927998854143
283011 Dorchester England United Kingdom 2.0119802013279195
134228 Dorfbeuern Salzburg Austria 1.9902576747419776
128724 Dorf an der Pram Upper Austria Austria 1.9833296956251587
1321355 Dortan Metropolitan France France 1.9776227091890408
1315478 Dorceau Orne France 1.9736348667137222
1325957 Dornes Metropolitan France France 1.9685499157074624
128696 Dorfgastein Salzburg Austria 1.9636229683432398
253055 Dorio Lombardy Italy 1.9618357225398109
243959 Dormelletto Piedmont Italy 1.959218938939536
238213 Montalto Dora Piedmont Italy 1.9535433086096983
133664 Dorfstetten Lower Austria Austria 1.9524907824177051
281738 San Lorenzo Dorsino Trentino-South Tyrol Italy 1.9522520129780585
248807 Dorno Lombardy Italy 1.9412333507161763
243488 Dorzano Piedmont Italy 1.9377142626583659
243102 Villar Dora Piedmont Italy 1.9357766265478624
1335169 Dorat Metropolitan France France 1.9351727247400161
1331360 Saint-Loup-du-Dorat Metropolitan France France 1.931783489011849
1320476 Dore-l''Église Metropolitan France France 1.917635375912257
1331044 Mont-Dore Metropolitan France France 1.9167107579976064
1311153 Le Dorat Metropolitan France France 1.9152004406282688
1335253 Doranges Metropolitan France France 1.9151827805546087
243184 San Dorligo della Valle Friuli-Venezia Giulia Italy 1.9108607720726831
707266 Le Puiset-Doré Metropolitan France France 1.9014402523677445
30643 Dorog Komárom-Esztergom County Hungary 1.8942489404455378
1345066 Monceaux-sur-Dordogne Metropolitan France France 1.8827287859986406
697194 Beaulieu-sur-Dordogne Metropolitan France France 1.8798332200213268
129077 Dorohoi Botoșani County Romania 1.7515260033280349
10073 Dorud Lorestan Province Iran 1.1916648053053223
439462 Doru Shahabad Jammu and Kashmir India 0.8152013466269584
 hidden batch(es)