{"id":16213,"date":"2022-10-14T17:11:41","date_gmt":"2022-10-14T11:41:41","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/"},"modified":"2022-10-14T17:11:41","modified_gmt":"2022-10-14T11:41:41","slug":"solved-custom-row-number-in-sql","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/","title":{"rendered":"[Solved] Custom row number in SQL"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-50615876\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"50615876\" data-parentid=\"50615105\" 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 you have a column in that table, that defines an order, you could get the section numbers with subqueries fetching the count of rows with lower ordered rows with the respective level.<\/p>\n<p>Assuming the column defining the order is <code>id<\/code> something looking like this could be what you want.<\/p>\n<pre><code>SELECT col1,\n       convert(varchar(max), (SELECT count(*)\n                                     FROM myTable chart1\n                                     WHERE chart1.id &lt;= chart.id\n                                           AND chart1.chart_level &lt;= 2))\n       +\n       CASE\n          WHEN (SELECT count(*)\n                       FROM myTable chart2\n                            WHERE chart2.id &lt;= chart.id\n                                  AND chart2.chart_level &gt; 2\n                                  AND (SELECT count(*)\n                                              FROM myTable chart3\n                                              WHERE chart3.id &lt;= chart.id\n                                                    AND chart3.chart_level &lt;= 2)\n                                       = (SELECT count(*)\n                                                 FROM myTable chart4\n                                                 WHERE chart4.id &lt;= chart2.id\n                                                       AND chart4.chart_level &lt;= 2)) = 0\n            THEN ''\n          ELSE\n            '.' + convert(varchar(max), (SELECT count(*)\n                                                FROM myTable chart2\n                                                WHERE chart2.id &lt;= chart.id\n                                                      AND chart2.chart_level &gt; 2\n                                                      AND (SELECT count(*)\n                                                                  FROM myTable chart3\n                                                                  WHERE chart3.id &lt;= chart.id\n                                                                        AND chart3.chart_level &lt;= 2)\n                                                           = (SELECT count(*)\n                                                                     FROM myTable chart4\n                                                                     WHERE chart4.id &lt;= chart2.id\n                                                                           AND chart4.chart_level &lt;= 2)))\n       END RowNo\n       FROM myTable chart;\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">0<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Custom row number in SQL <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] If you have a column in that table, that defines an order, you could get the section numbers with subqueries fetching the count of rows with lower ordered rows with the respective level. Assuming the column defining the order is id something looking like this could be what you want. SELECT col1, convert(varchar(max), (SELECT &#8230; <a title=\"[Solved] Custom row number in SQL\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/\" aria-label=\"More on [Solved] Custom row number in SQL\">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,500],"class_list":["post-16213","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql","tag-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>[Solved] Custom row number in SQL - 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-custom-row-number-in-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Custom row number in SQL - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] If you have a column in that table, that defines an order, you could get the section numbers with subqueries fetching the count of rows with lower ordered rows with the respective level. Assuming the column defining the order is id something looking like this could be what you want. SELECT col1, convert(varchar(max), (SELECT ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-14T11:41:41+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-custom-row-number-in-sql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-custom-row-number-in-sql\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Custom row number in SQL\",\"datePublished\":\"2022-10-14T11:41:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-custom-row-number-in-sql\\\/\"},\"wordCount\":63,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"sql\",\"sql-server\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-custom-row-number-in-sql\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-custom-row-number-in-sql\\\/\",\"name\":\"[Solved] Custom row number in SQL - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-10-14T11:41:41+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-custom-row-number-in-sql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-custom-row-number-in-sql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-custom-row-number-in-sql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Custom row number in SQL\"}]},{\"@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\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Custom row number in SQL - 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-custom-row-number-in-sql\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Custom row number in SQL - JassWeb","og_description":"[ad_1] If you have a column in that table, that defines an order, you could get the section numbers with subqueries fetching the count of rows with lower ordered rows with the respective level. Assuming the column defining the order is id something looking like this could be what you want. SELECT col1, convert(varchar(max), (SELECT ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/","og_site_name":"JassWeb","article_published_time":"2022-10-14T11:41:41+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-custom-row-number-in-sql\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Custom row number in SQL","datePublished":"2022-10-14T11:41:41+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/"},"wordCount":63,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql","sql-server"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/","url":"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/","name":"[Solved] Custom row number in SQL - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-14T11:41:41+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-custom-row-number-in-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Custom row number in SQL"}]},{"@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\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","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\/16213","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=16213"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/16213\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=16213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=16213"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=16213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}