{"id":31916,"date":"2023-01-25T13:32:22","date_gmt":"2023-01-25T08:02:22","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/"},"modified":"2023-01-25T13:32:22","modified_gmt":"2023-01-25T08:02:22","slug":"solved-sql-statement-for-getting-top-5","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/","title":{"rendered":"[Solved] SQL statement for getting top 5"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-24472333\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"24472333\" data-parentid=\"24472044\" data-score=\"0\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>If I understand correctly, you want the top 5 countries that appear the most times in the <code>ads<\/code> table, and for each one of those countries you want to display its most frequent section.<\/p>\n<p>The query below selects the top 5 countries by grouping the ads table by country and ordering the count and uses a subquery to select each country&#8217;s most popular section.<\/p>\n<pre><code>SELECT TOP 5 Country , (\n    SELECT TOP 1 Section FROM ads a2\n    WHERE a2.Country = a1.Country\n    GROUP BY Section\n    ORDER BY COUNT(*) DESC\n) TopSection\nFROM ads a1\nGROUP BY Country\nORDER BY COUNT(*) DESC\n<\/code><\/pre>\n<p>To display the top 3 sections for the top 5 countries<\/p>\n<pre><code>SELECT TOP 5 Country , (\n    SELECT TOP 1 Section FROM (\n        SELECT Section, \n        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rank\n        FROM ads a2    \n        WHERE a2.Country = a1.Country\n        GROUP BY Section\n    ) t1 WHERE rank = 1\n) Top1Section, (\n    SELECT TOP 1 Section FROM (\n        SELECT Section, \n        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rank\n        FROM ads a2    \n        WHERE a2.Country = a1.Country\n        GROUP BY Section\n    ) t1 WHERE rank = 2\n) Top2Section, (\n    SELECT TOP 1 Section FROM (\n        SELECT Section, \n        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rank\n        FROM ads a2    \n        WHERE a2.Country = a1.Country\n        GROUP BY Section\n    ) t1 WHERE rank = 3\n) Top3Section\nFROM ads a1\nGROUP BY Country\nORDER BY COUNT(*) DESC\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">7<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved SQL statement for getting top 5 <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] If I understand correctly, you want the top 5 countries that appear the most times in the ads table, and for each one of those countries you want to display its most frequent section. The query below selects the top 5 countries by grouping the ads table by country and ordering the count and &#8230; <a title=\"[Solved] SQL statement for getting top 5\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/\" aria-label=\"More on [Solved] SQL statement for getting top 5\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[341],"class_list":["post-31916","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] SQL statement for getting top 5 - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] SQL statement for getting top 5 - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] If I understand correctly, you want the top 5 countries that appear the most times in the ads table, and for each one of those countries you want to display its most frequent section. The query below selects the top 5 countries by grouping the ads table by country and ordering the count and ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2023-01-25T08:02:22+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] SQL statement for getting top 5\",\"datePublished\":\"2023-01-25T08:02:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/\"},\"wordCount\":85,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/\",\"name\":\"[Solved] SQL statement for getting top 5 - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2023-01-25T08:02:22+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] SQL statement for getting top 5\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] SQL statement for getting top 5 - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] SQL statement for getting top 5 - JassWeb","og_description":"[ad_1] If I understand correctly, you want the top 5 countries that appear the most times in the ads table, and for each one of those countries you want to display its most frequent section. The query below selects the top 5 countries by grouping the ads table by country and ordering the count and ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/","og_site_name":"JassWeb","article_published_time":"2023-01-25T08:02:22+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] SQL statement for getting top 5","datePublished":"2023-01-25T08:02:22+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/"},"wordCount":85,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/","name":"[Solved] SQL statement for getting top 5 - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2023-01-25T08:02:22+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-statement-for-getting-top-5\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] SQL statement for getting top 5"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/31916","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=31916"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/31916\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=31916"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=31916"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=31916"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}