1"use strict";(globalThis.webpackChunkstackql_io||=[]).push([[6955],{6175(e,n,r){r.r(n),r.d(n,{assets:()=>c,contentTitle:()=>a,default:()=>u,frontMatter:()=>o,metadata:()=>t,toc:()=>d});const t=JSON.parse('{"id":"language-spec/functions/window/row_number","title":"ROW_NUMBER","description":"Query and Deploy Cloud Infrastructure and Resources using SQL","source":"@site/docs/language-spec/functions/window/row_number.md","sourceDirName":"language-spec/functions/window","slug":"/language-spec/functions/window/row_number","permalink":"/docs/language-spec/functions/window/row_number","draft":false,"unlisted":false,"editUrl":"https://github.com/stackql/stackql.io/edit/main/docs/language-spec/functions/window/row_number.md","tags":[],"version":"current","lastUpdatedAt":1764878918000,"frontMatter":{"title":"ROW_NUMBER","hide_title":false,"hide_table_of_contents":false,"keywords":["stackql","infrastructure-as-code","configuration-as-data","cloud inventory"],"description":"Query and Deploy Cloud Infrastructure and Resources using SQL","image":"/img/stackql-featured-image.png"},"sidebar":"docsSidebar","previous":{"title":"RANK","permalink":"/docs/language-spec/functions/window/rank"},"next":{"title":"Data Types","permalink":"/docs/language-spec/data-types"}}');var s=r(74848),i=r(28453);const o={title:"ROW_NUMBER",hide_title:!1,hide_table_of_contents:!1,keywords:["stackql","infrastructure-as-code","configuration-as-data","cloud inventory"],description:"Query and Deploy Cloud Infrastructure and Resources using SQL",image:"/img/stackql-featured-image.png"},a=void 0,c={},d=[{value:"Syntax",id:"syntax",level:2},{value:"Arguments",id:"arguments",level:2},{value:"Return Value(s)",id:"return-values",level:2},{value:"Examples",id:"examples",level:2},{value:"Rank contributors within a repository",id:"rank-contributors-within-a-repository",level:3},{value:"Track issue sequence over time",id:"track-issue-sequence-over-time",level:3},{value:"Compare <code>ROW_NUMBER</code> with <code>RANK</code> and <code>DENSE_RANK</code>",id:"compare-row_number-with-rank-and-dense_rank",level:3}];function l(e){const n={a:"a",code:"code",em:"em",h2:"h2",h3:"h3",hr:"hr",p:"p",pre:"pre",strong:"strong",...(0,i.R)(),...e.components};return(0,s.jsxs)(s.Fragment,{children:[(0,s.jsxs)(n.p,{children:["Returns the number of the row within the current partition. Rows are numbered starting from 1 in the order defined by the ",(0,s.jsx)(n.code,{children:"ORDER BY"})," clause in the window definition, or in arbitrary order otherwise."]}),"\n",(0,s.jsxs)(n.p,{children:["See also:\r\n",(0,s.jsxs)(n.a,{href:"/docs/language-spec/select",children:["[",(0,s.jsx)(n.code,{children:"SELECT"}),"]"]})," ",(0,s.jsxs)(n.a,{href:"/docs/language-spec/functions/window/rank",children:["[",(0,s.jsx)(n.code,{children:"RANK"}),"]"]})," ",(0,s.jsxs)(n.a,{href:"/docs/language-spec/functions/window/dense_rank",children:["[",(0,s.jsx)(n.code,{children:"DENSE_RANK"}),"]"]})]}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"syntax",children:"Syntax"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"SELECT ROW_NUMBER() OVER ([PARTITION BY column] ORDER BY column [ASC|DESC])\r\nFROM <multipartIdentifier>;\n"})}),"\n",(0,s.jsx)(n.h2,{id:"arguments",children:"Arguments"}),"\n",(0,s.jsxs)(n.p,{children:[(0,s.jsx)(n.strong,{children:(0,s.jsx)(n.em,{children:"PARTITION BY column"})}),"\r\nOptional. Divides the result set into partitions. The ",(0,s.jsx)(n.code,{children:"ROW_NUMBER"})," function is applied to each partition separately and resets the row number for each partition."]}),"\n",(0,s.jsxs)(n.p,{children:[(0,s.jsx)(n.strong,{children:(0,s.jsx)(n.em,{children:"ORDER BY column"})}),"\r\nSpecifies the order in which the row numbers are assigned. Required for meaningful results."]}),"\n",(0,s.jsx)(n.h2,{id:"return-values",children:"Return Value(s)"}),"\n",(0,s.jsx)(n.p,{children:"Returns an integer representing the sequential row number within the partition, starting at 1."}),"\n",(0,s.jsx)(n.hr,{}),"\n",(0,s.jsx)(n.h2,{id:"examples",children:"Examples"}),"\n",(0,s.jsx)(n.h3,{id:"rank-contributors-within-a-repository",children:"Rank contributors within a repository"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"-- Rank contributors within a repository\r\nSELECT\r\n login,\r\n contributions,\r\n ROW_NUMBER() OVER (ORDER BY contributions DESC) as contribution_rank\r\nFROM github.repos.contributors\r\nWHERE owner = 'stackql'\r\n AND repo = 'stackql';\n"})}),"\n",(0,s.jsx)(n.h3,{id:"track-issue-sequence-over-time",children:"Track issue sequence over time"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"-- Track issue growth over time\r\nSELECT\r\n number,\r\n title,\r\n state,\r\n created_at,\r\n ROW_NUMBER() OVER (ORDER BY created_at) as issue_sequence\r\nFROM github.issues.issues\r\nWHERE owner = 'stackql'\r\n AND repo = 'stackql'\r\nORDER BY created_at;\n"})}),"\n",(0,s.jsxs)(n.h3,{id:"compare-row_number-with-rank-and-dense_rank",children:["Compare ",(0,s.jsx)(n.code,{children:"ROW_NUMBER"})," with ",(0,s.jsx)(n.code,{children:"RANK"})," and ",(0,s.jsx)(n.code,{children:"DENSE_RANK"})]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-sql",children:"-- Compare RANK vs DENSE_RANK vs ROW_NUMBER for contributors\r\nSELECT\r\n login,\r\n contributions,\r\n RANK() OVER (ORDER BY contributions DESC) as rank,\r\n DENSE_RANK() OVER (ORDER BY contributions DESC) as dense_rank,\r\n ROW_NUMBER() OVER (ORDER BY contributions DESC) as row_num\r\nFROM github.repos.contributors\r\nWHERE owner = 'stackql'\r\n AND repo = 'stackql';\n"})}),"\n",(0,s.jsxs)(n.p,{children:["For more information, see ",(0,s.jsx)(n.a,{href:"https://sqlite.org/windowfunctions.html#built-in_window_functions",children:"https://sqlite.org/windowfunctions.html#built-in_window_functions"}),"."]})]})}function u(e={}){const{wrapper:n}={...(0,i.R)(),...e.components};return n?(0,s.jsx)(n,{...e,children:(0,s.jsx)(l,{...e})}):l(e)}},28453(e,n,r){r.d(n,{R:()=>o,x:()=>a});var t=r(96540);const s={},i=t.createContext(s);function o(e){const n=t.useContext(i);return t.useMemo(function(){return"function"==typeof e?e(n):{...n,...e}},[n,e])}function a(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(s):e.components||s:o(e.components),t.createElement(i.Provider,{value:n},e.children)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.