{"id":682,"date":"2024-07-05T11:41:09","date_gmt":"2024-07-05T03:41:09","guid":{"rendered":"https:\/\/info.juliusgoh.life\/?p=682"},"modified":"2024-07-05T11:42:35","modified_gmt":"2024-07-05T03:42:35","slug":"mysql-row_number-usage-and-example","status":"publish","type":"post","link":"https:\/\/info.juliusgoh.life\/?p=682","title":{"rendered":"MYSQL Row_number usage and example"},"content":{"rendered":"\n

Imagine data as below:-<\/p>\n\n\n\n

category_id<\/th>item_name<\/th><\/tr><\/thead>
1<\/td>Item A1<\/td><\/tr>
1<\/td>Item A2<\/td><\/tr>
1<\/td>Item A2<\/td><\/tr>
2<\/td>Item B1<\/td><\/tr>
2<\/td>Item B1<\/td><\/tr>
2<\/td>Item B2<\/td><\/tr>
3<\/td>Item C1<\/td><\/tr>
3<\/td>Item C2<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
SELECT \n    category_id, \n    item_name,\n    ROW_NUMBER() OVER(PARTITION BY category_id, item_name ORDER BY category_id, item_name) AS displayOrder\nFROM \n    items\nORDER BY \n    category_id, displayOrder;<\/code><\/pre>\n\n\n\n
In this query:<\/p>\n\n\n\n
    \n
  • ROW_NUMBER() OVER(PARTITION BY category_id, item_name ORDER BY category_id, item_name)<\/strong>: The PARTITION BY<\/code> clause partitions the result set by both category_id<\/code> and item_name<\/code>. ROW_NUMBER()<\/code> then assigns a sequential number (displayOrder<\/code>) starting from 1 within each partition, ordered first by category_id<\/code> and then by item_name<\/code>.<\/li>\n\n\n\n
  • SELECT category_id, item_name<\/strong>: Selects only category_id<\/code> and item_name<\/code> from the items<\/code> table.<\/li>\n\n\n\n
  • ORDER BY category_id, displayOrder<\/strong>: Orders the result set first by category_id<\/code> and then by displayOrder<\/code>, ensuring that items within each category_id<\/code> and item_name<\/code> group are ordered sequentially based on category_id<\/code> and item_name<\/code>.<\/li>\n<\/ul>\n\n\n\n
    Now, let’s see the result of executing this modified query on our sample data:<\/p>\n\n\n\n
    category_id<\/th>item_name<\/th>displayOrder<\/th><\/tr><\/thead>
    1<\/td>Item A1<\/td>1<\/td><\/tr>
    1<\/td>Item A2<\/td>2<\/td><\/tr>
    1<\/td>Item A2<\/td>3<\/td><\/tr>
    2<\/td>Item B1<\/td>1<\/td><\/tr>
    2<\/td>Item B1<\/td>2<\/td><\/tr>
    2<\/td>Item B2<\/td>3<\/td><\/tr>
    3<\/td>Item C1<\/td>1<\/td><\/tr>
    3<\/td>Item C2<\/td>2<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    In this result:<\/p>\n\n\n\n
      \n
    • The item_id<\/code> is not included.<\/li>\n\n\n\n
    • displayOrder<\/code> starts from 1 within each combination of category_id<\/code> and item_name<\/code>, ordered first by category_id<\/code> and then by item_name<\/code>.<\/li>\n\n\n\n
    • Items are ordered first by category_id<\/code>, and within each category_id<\/code>, items with the same item_name<\/code> are ordered by item_name<\/code>.<\/li>\n<\/ul>\n\n\n\n
      This query structure maintains the sequential ordering (displayOrder<\/code>) within each unique combination of category_id<\/code> and item_name<\/code>, ensuring the rows are ordered by both criteria within their respective partitions.<\/p>\n","protected":false},"excerpt":{"rendered":"
      Imagine data as below:- category_id item_name 1 Item A1 1 Item A2 1 Item A2 2 Item B1 2 Item B1 2 Item B2 3 Item C1 3 Item C2 In this query: Now, let’s see the result of executing this modified query on our sample data: category_id item_name displayOrder 1 Item A1 1 1 […]<\/p>\n","protected":false},"author":1,"featured_media":217,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"image","meta":{"footnotes":""},"categories":[3],"tags":[],"_links":{"self":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/682"}],"collection":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=682"}],"version-history":[{"count":3,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/682\/revisions"}],"predecessor-version":[{"id":704,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/682\/revisions\/704"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/media\/217"}],"wp:attachment":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=682"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=682"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=682"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}