{"id":795,"date":"2019-07-11T16:00:17","date_gmt":"2019-07-11T09:00:17","guid":{"rendered":"http:\/\/www.oopsbox.com\/?p=795"},"modified":"2019-07-11T16:10:04","modified_gmt":"2019-07-11T09:10:04","slug":"mysql-get-first-value-or-last-value-in-each-group","status":"publish","type":"post","link":"https:\/\/www.oopsbox.com\/?p=795","title":{"rendered":"MySQL Get First value or Last value in each group"},"content":{"rendered":"<p>\u0e43\u0e19 MySQL \u0e15\u0e31\u0e49\u0e07\u0e41\u0e15\u0e48\u0e40\u0e27\u0e2d\u0e23\u0e4c\u0e0a\u0e31\u0e48\u0e19 8.0 \u0e40\u0e1b\u0e47\u0e19\u0e15\u0e49\u0e19\u0e44\u0e1b \u0e16\u0e49\u0e32\u0e15\u0e49\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e14\u0e36\u0e07<strong>\u0e04\u0e48\u0e32\u0e41\u0e23\u0e01<\/strong>\u0e41\u0e25\u0e30<strong>\u0e04\u0e48\u0e32\u0e2a\u0e38\u0e14\u0e17\u0e49\u0e32\u0e22<\/strong>\u0e43\u0e19\u0e01\u0e23\u0e38\u0e4a\u0e1b\u0e19\u0e31\u0e49\u0e19 \u0e46 \u0e2d\u0e2d\u0e01\u0e21\u0e32 \u0e2a\u0e32\u0e21\u0e32\u0e23\u0e16\u0e43\u0e0a\u0e49 Function<\/p>\n<ul>\n<li>FIRST_VALUE()<br \/>\n<em>FIRST_VALUE(expr) [null_treatment] over_clause<\/em><br \/>\n<em>Returns the value of expr from the first row of the window frame.<\/em><\/li>\n<li>LAST_VALUE()<br \/>\n<em>LAST_VALUE(expr) [null_treatment] over_clause<\/em><br \/>\n<em>Returns the value of expr from the last row of the window frame.<\/em><\/li>\n<\/ul>\n<p><strong>\u0e15\u0e31\u0e27\u0e2d\u0e22\u0e48\u0e32\u0e07\u0e01\u0e32\u0e23\u0e43\u0e0a\u0e49\u0e07\u0e32\u0e19<\/strong><\/p>\n<p><strong>\u0e42\u0e08\u0e17\u0e22\u0e4c<\/strong> \u0e21\u0e35\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 CKD Stage (\u0e2a\u0e21\u0e21\u0e38\u0e15\u0e34) \u0e40\u0e23\u0e35\u0e22\u0e07\u0e15\u0e32\u0e21\u0e23\u0e32\u0e22\u0e1b\u0e35\u0e07\u0e1a\u0e1b\u0e23\u0e30\u0e21\u0e32\u0e13 \u0e16\u0e49\u0e32\u0e15\u0e49\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e17\u0e23\u0e32\u0e1a Stage \u0e41\u0e23\u0e01\u0e41\u0e25\u0e30\u0e2a\u0e38\u0e14\u0e17\u0e49\u0e32\u0e22\u0e17\u0e35\u0e48\u0e15\u0e23\u0e27\u0e08\u0e1e\u0e1a\u0e43\u0e19\u0e23\u0e32\u0e22\u0e19\u0e31\u0e49\u0e19 \u0e46 \u0e2b\u0e32\u0e44\u0e14\u0e49\u0e08\u0e32\u0e01<\/p>\n<p>\u0e40\u0e23\u0e34\u0e48\u0e21\u0e15\u0e49\u0e19\u0e04\u0e37\u0e2d\u0e2a\u0e23\u0e49\u0e32\u0e07\u0e15\u0e32\u0e23\u0e32\u0e07\u0e17\u0e14\u0e2a\u0e2d\u0e1a<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">CREATE TABLE ckdstage(vn INT(11) AUTO_INCREMENT,\r\n    hn VARCHAR(50) NOT NULL,\r\n    fiscal_year INT NOT NULL,\r\n    stage INT(1) NOT NULL,\r\n    PRIMARY KEY(vn)\r\n);\r\n \r\nINSERT INTO ckdstage(vn,hn,fiscal_year,stage)\r\nVALUES\r\n(NULL,'1945',2016, 4),\r\n(NULL,'1945',2017, 3),\r\n(NULL,'1945',2018, 5),\r\n(NULL,'1945',2019, 3),\r\n(NULL,'2311',2016, 4),\r\n(NULL,'2311',2017, 5),\r\n(NULL,'2311',2018, 3),\r\n(NULL,'2311',2019, 2);<\/pre>\n<p>\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e17\u0e35\u0e48\u0e44\u0e14\u0e49\u0e08\u0e30\u0e40\u0e1b\u0e47\u0e19\u0e15\u0e32\u0e21\u0e19\u0e35\u0e49 \u0e16\u0e49\u0e32\u0e21\u0e2d\u0e07\u0e14\u0e49\u0e27\u0e22\u0e2a\u0e32\u0e22\u0e15\u0e32\u0e04\u0e48\u0e32\u0e17\u0e35\u0e48\u0e40\u0e23\u0e32\u0e15\u0e49\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e08\u0e30\u0e15\u0e32\u0e21\u0e17\u0e35\u0e48\u0e25\u0e39\u0e01\u0e28\u0e23\u0e0a\u0e35\u0e49<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-796\" src=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711115326.png\" alt=\"\" width=\"778\" height=\"351\" srcset=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711115326.png 778w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711115326-300x135.png 300w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711115326-768x346.png 768w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711115326-700x316.png 700w\" sizes=\"auto, (max-width: 778px) 100vw, 778px\" \/><\/p>\n<p>\u0e2b\u0e32\u0e1c\u0e25\u0e25\u0e31\u0e1e\u0e18\u0e4c\u0e15\u0e32\u0e21\u0e42\u0e08\u0e17\u0e22\u0e4c<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT \r\n    hn,\r\n    fiscal_year,\r\n    FIRST_VALUE(stage) OVER (\r\n      \tPARTITION BY hn\r\n        ORDER BY fiscal_year\r\n    ) first_stage,\t\r\n    LAST_VALUE(stage) OVER (\r\n        PARTITION BY hn      \r\n        ORDER BY fiscal_year\r\n        RANGE BETWEEN\r\n            UNBOUNDED PRECEDING AND\r\n            UNBOUNDED FOLLOWING\r\n    ) last_stage\r\nFROM ckdstage;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-797\" src=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711115805.png\" alt=\"\" width=\"787\" height=\"368\" srcset=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711115805.png 787w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711115805-300x140.png 300w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711115805-768x359.png 768w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711115805-700x327.png 700w\" sizes=\"auto, (max-width: 787px) 100vw, 787px\" \/><\/p>\n<p>\u0e08\u0e30\u0e40\u0e2b\u0e47\u0e19\u0e44\u0e14\u0e49\u0e27\u0e48\u0e32\u0e1c\u0e25\u0e25\u0e31\u0e1e\u0e18\u0e4c\u0e17\u0e35\u0e44\u0e14\u0e49\u0e08\u0e30\u0e14\u0e36\u0e07\u0e04\u0e48\u0e32\u0e41\u0e23\u0e01\u0e2a\u0e38\u0e14\u0e41\u0e25\u0e30\u0e04\u0e48\u0e32\u0e2a\u0e38\u0e14\u0e17\u0e49\u0e32\u0e22\u0e15\u0e32\u0e21\u0e01\u0e23\u0e38\u0e4a\u0e1b\u0e02\u0e2d\u0e07\u0e04\u0e2d\u0e25\u0e31\u0e21\u0e20\u0e4c hn \u0e41\u0e15\u0e48\u0e02\u0e48\u0e32\u0e27\u0e23\u0e49\u0e32\u0e22\u0e01\u0e47\u0e04\u0e37\u0e2d\u0e1f\u0e31\u0e07\u0e01\u0e4c\u0e0a\u0e31\u0e48\u0e19\u0e19\u0e35\u0e49\u0e44\u0e21\u0e48\u0e21\u0e35\u0e43\u0e2b\u0e49\u0e43\u0e0a\u0e49\u0e43\u0e19 MySQL \u0e40\u0e27\u0e2d\u0e23\u0e4c\u0e0a\u0e31\u0e48\u0e19\u0e40\u0e01\u0e48\u0e32 55555 \u0e0b\u0e36\u0e48\u0e07\u0e01\u0e47\u0e2a\u0e32\u0e21\u0e32\u0e23\u0e16\u0e41\u0e01\u0e49\u0e1b\u0e31\u0e0d\u0e2b\u0e32\u0e42\u0e14\u0e22\u0e01\u0e32\u0e23\u0e08\u0e33\u0e25\u0e2d\u0e07\u0e1f\u0e31\u0e07\u0e01\u0e4c\u0e0a\u0e31\u0e48\u0e19 First(), Last()\u00a0 \u0e02\u0e36\u0e49\u0e19\u0e21\u0e32\u0e43\u0e0a\u0e49\u0e07\u0e32\u0e19\u0e40\u0e2d\u0e07 \u0e15\u0e31\u0e27\u0e2d\u0e22\u0e48\u0e32\u0e07<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT \r\n    t1.hn,\r\n    GROUP_CONCAT(t1.stage ORDER BY t1.vn ASC) AS stagelist_asc,\r\n    GROUP_CONCAT(t1.stage ORDER BY t1.vn DESC) AS stagelist_desc,\r\n    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(t1.stage ORDER BY t1.vn ASC), ',', 1) , ',' , -1) AS first_stage,\r\n    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(t1.stage ORDER BY t1.vn DESC), ',', 1) , ',' , -1) AS last_stage\r\nFROM ckdstage AS t1\r\nGROUP BY t1.hn<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-798\" src=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711155335.png\" alt=\"\" width=\"789\" height=\"130\" srcset=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711155335.png 789w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711155335-300x49.png 300w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711155335-768x127.png 768w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/DeepinScreenshot_select-area_20190711155335-700x115.png 700w\" sizes=\"auto, (max-width: 789px) 100vw, 789px\" \/><\/p>\n<p>\u0e08\u0e1a\u0e1b\u0e34\u0e4a\u0e07 !! ^__^<\/p>\n<p>\u0e1b.\u0e25.<\/p>\n<ul>\n<li>\u0e44\u0e1b\u0e40\u0e25\u0e48\u0e19\u0e01\u0e31\u0e19\u0e44\u0e14\u0e49\u0e17\u0e35\u0e48 <a href=\"https:\/\/www.db-fiddle.com\/f\/7P1LFYkebGbPpHPnG3jjJa\/0\">https:\/\/www.db-fiddle.com\/f\/7P1LFYkebGbPpHPnG3jjJa\/0<\/a><\/li>\n<li><a href=\"http:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-first_value-function\/\">First Value Function<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<div id=\"s-share-buttons\" class=\"\"><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t<div class=\"fb-share-button\" data-href=\"https:\/\/www.oopsbox.com\/?p=795\" data-type=\"button_count\"><\/div><\/div><div class=\"s-single-share\"><a href=\"https:\/\/twitter.com\/share\" class=\"twitter-share-button\"><\/a>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<div class=\"g-plusone\" data-size=\"medium\"><\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<a href=\"\/\/www.pinterest.com\/pin\/create\/button\/\" data-pin-do=\"buttonBookmark\"  data-pin-color=\"red\"><img decoding=\"async\" src=\"\/\/assets.pinterest.com\/images\/pidgets\/pinit_fg_en_rect_red_20.png\" \/><\/a>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<script type=\"text\/javascript\" async defer src=\"\/\/assets.pinterest.com\/js\/pinit.js\"><\/script>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t<a href=\"mailto:?Subject=MySQL%20Get%20First%20value%20or%20Last%20value%20in%20each%20group&Body=Here%20is%20the%20link%20to%20the%20article:%20https:\/\/www.oopsbox.com\/?p=795\" title=\"Email\" class=\"s3-email\"><img decoding=\"async\" src=\"https:\/\/www.oopsbox.com\/wp-content\/plugins\/simple-social-share\/includes\/..\/images\/share-email.png\"><\/a>\n\t\t\t\t\t\t\t\t\t\t\t<\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>\u0e43\u0e19 MySQL \u0e15\u0e31\u0e49\u0e07\u0e41\u0e15\u0e48\u0e40\u0e27\u0e2d\u0e23\u0e4c\u0e0a\u0e31\u0e48\u0e19 8.0 \u0e40\u0e1b\u0e47\u0e19\u0e15\u0e49\u0e19\u0e44\u0e1b \u0e16\u0e49\u0e32\u0e15\u0e49\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e14\u0e36\u0e07\u0e04\u0e48\u0e32\u0e41\u0e23\u0e01\u0e41\u0e25\u0e30\u0e04\u0e48\u0e32\u0e2a\u0e38\u0e14\u0e17\u0e49\u0e32\u0e22\u0e43\u0e19\u0e01\u0e23\u0e38\u0e4a\u0e1b\u0e19\u0e31\u0e49\u0e19 \u0e46 \u0e2d\u0e2d\u0e01\u0e21\u0e32 \u0e2a\u0e32\u0e21\u0e32\u0e23\u0e16\u0e43\u0e0a\u0e49 Function FIRST_VALUE() FIRST_VALUE(expr) [null_treatment] over_clause Returns the value of expr from the first row of the window frame. LAST_VALUE() LAST_VALUE(expr) [null_treatment] over_clause Returns the value of expr from the last row of the window frame. \u0e15\u0e31\u0e27\u0e2d\u0e22\u0e48\u0e32\u0e07\u0e01\u0e32\u0e23\u0e43\u0e0a\u0e49\u0e07\u0e32\u0e19 \u0e42\u0e08\u0e17\u0e22\u0e4c \u0e21\u0e35\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 CKD Stage (\u0e2a\u0e21\u0e21\u0e38\u0e15\u0e34) \u0e40\u0e23\u0e35\u0e22\u0e07\u0e15\u0e32\u0e21\u0e23\u0e32\u0e22\u0e1b\u0e35\u0e07\u0e1a\u0e1b\u0e23\u0e30\u0e21\u0e32\u0e13 \u0e16\u0e49\u0e32\u0e15\u0e49\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e17\u0e23\u0e32\u0e1a Stage \u0e41\u0e23\u0e01\u0e41\u0e25\u0e30\u0e2a\u0e38\u0e14\u0e17\u0e49\u0e32\u0e22\u0e17\u0e35\u0e48\u0e15\u0e23\u0e27\u0e08\u0e1e\u0e1a\u0e43\u0e19\u0e23\u0e32\u0e22\u0e19\u0e31\u0e49\u0e19 \u0e46 &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.oopsbox.com\/?p=795\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;MySQL Get First value or Last value in each group&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":799,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[44],"tags":[12,77],"class_list":["post-795","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-how-to","tag-mysql","tag-simulate","entry"],"views":5054,"_links":{"self":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/795","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=795"}],"version-history":[{"count":3,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/795\/revisions"}],"predecessor-version":[{"id":802,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/795\/revisions\/802"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/media\/799"}],"wp:attachment":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=795"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=795"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=795"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}