{"id":4162,"date":"2024-09-26T12:14:07","date_gmt":"2024-09-26T04:14:07","guid":{"rendered":"https:\/\/five.co\/?p=4162"},"modified":"2026-05-07T08:32:41","modified_gmt":"2026-05-07T00:32:41","slug":"sql-first-value","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-first-value\/","title":{"rendered":"SQL FIRST_VALUE: Practical Guide"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Breakdown of SQL FIRST_VALUE<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The SQL FIRST_VALUE function is a window function that returns the first value in an ordered set of values. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It&#8217;s a tool for data analysis, allowing you to retrieve specific values from a group of rows based on a particular ordering. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s dive into how FIRST_VALUE works and explore its practical applications.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-jvmfb wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-jvmfb \"><div class=\"eb-toc-container eb-toc-jvmfb  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Breakdown of SQL FIRST_VALUE&quot;,&quot;text&quot;:&quot;Breakdown of SQL FIRST_VALUE&quot;,&quot;link&quot;:&quot;breakdown-of-sql-first_value&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Understanding FIRST_VALUE&quot;,&quot;text&quot;:&quot;Understanding FIRST_VALUE&quot;,&quot;link&quot;:&quot;understanding-first_value&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Partitioning with FIRST_VALUE&quot;,&quot;text&quot;:&quot;Partitioning with FIRST_VALUE&quot;,&quot;link&quot;:&quot;partitioning-with-first_value&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-World Use Case: Sales Analysis&quot;,&quot;text&quot;:&quot;Real-World Use Case: Sales Analysis&quot;,&quot;link&quot;:&quot;real-world-use-case-sales-analysis&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;FIRST_VALUE vs. MIN&quot;,&quot;text&quot;:&quot;FIRST_VALUE vs. MIN&quot;,&quot;link&quot;:&quot;first_value-vs-min&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Handling NULL Values&quot;,&quot;text&quot;:&quot;Handling NULL Values&quot;,&quot;link&quot;:&quot;handling-null-values&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;FIRST_VALUE with IGNORE NULLS&quot;,&quot;text&quot;:&quot;FIRST_VALUE with IGNORE NULLS&quot;,&quot;link&quot;:&quot;first_value-with-ignore-nulls&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Combining FIRST_VALUE with Other Window Functions&quot;,&quot;text&quot;:&quot;Combining FIRST_VALUE with Other Window Functions&quot;,&quot;link&quot;:&quot;combining-first_value-with-other-window-functions&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Using FIRST_VALUE for Time Series Analysis&quot;,&quot;text&quot;:&quot;Using FIRST_VALUE for Time Series Analysis&quot;,&quot;link&quot;:&quot;using-first_value-for-time-series-analysis&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Performance Considerations&quot;,&quot;text&quot;:&quot;Performance Considerations&quot;,&quot;link&quot;:&quot;performance-considerations&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Conclusion&quot;,&quot;text&quot;:&quot;Conclusion&quot;,&quot;link&quot;:&quot;conclusion&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Breakdown of SQL FIRST_VALUE&quot;,&quot;value&quot;:&quot;breakdown-of-sql-first_value&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Understanding FIRST_VALUE&quot;,&quot;value&quot;:&quot;understanding-first_value&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Partitioning with FIRST_VALUE&quot;,&quot;value&quot;:&quot;partitioning-with-first_value&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Real-World Use Case: Sales Analysis&quot;,&quot;value&quot;:&quot;real-world-use-case-sales-analysis&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;FIRST_VALUE vs. MIN&quot;,&quot;value&quot;:&quot;first_value-vs-min&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Handling NULL Values&quot;,&quot;value&quot;:&quot;handling-null-values&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;FIRST_VALUE with IGNORE NULLS&quot;,&quot;value&quot;:&quot;first_value-with-ignore-nulls&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Combining FIRST_VALUE with Other Window Functions&quot;,&quot;value&quot;:&quot;combining-first_value-with-other-window-functions&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Using FIRST_VALUE for Time Series Analysis&quot;,&quot;value&quot;:&quot;using-first_value-for-time-series-analysis&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Performance Considerations&quot;,&quot;value&quot;:&quot;performance-considerations&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Conclusion&quot;,&quot;value&quot;:&quot;conclusion&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#breakdown-of-sql-first_value\">Breakdown of SQL FIRST_VALUE<\/a><li><a href=\"#understanding-first_value\">Understanding FIRST_VALUE<\/a><li><a href=\"#partitioning-with-first_value\">Partitioning with FIRST_VALUE<\/a><li><a href=\"#real-world-use-case-sales-analysis\">Real-World Use Case: Sales Analysis<\/a><li><a href=\"#first_value-vs-min\">FIRST_VALUE vs. MIN<\/a><li><a href=\"#handling-null-values\">Handling NULL Values<\/a><li><a href=\"#first_value-with-ignore-nulls\">FIRST_VALUE with IGNORE NULLS<\/a><li><a href=\"#combining-first_value-with-other-window-functions\">Combining FIRST_VALUE with Other Window Functions<\/a><li><a href=\"#using-first_value-for-time-series-analysis\">Using FIRST_VALUE for Time Series Analysis<\/a><li><a href=\"#performance-considerations\">Performance Considerations<\/a><li><a href=\"#conclusion\">Conclusion<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Understanding FIRST_VALUE<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The basic syntax of FIRST_VALUE is as follows:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>FIRST_VALUE(expression) OVER (\n    &#091;PARTITION BY partition_expression, ... &#093;\n    ORDER BY sort_expression &#091;ASC | DESC&#093;, ...\n)<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(expression) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #E06C75\">&#091;PARTITION BY partition_expression, ... &#093;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> sort_expression <\/span><span style=\"color: #E06C75\">&#091;ASC | DESC&#093;<\/span><span style=\"color: #ABB2BF\">, ...<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s what each part means:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>expression<\/code>: The column or expression whose first value you want to retrieve.<\/li>\n\n\n\n<li><code>PARTITION BY<\/code>: Optional. Divides the result set into partitions to which the function is applied.<\/li>\n\n\n\n<li><code>ORDER BY<\/code>: Specifies the order of rows within each partition.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s start with a simple example:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    product_name,\n    category,\n    price,\n    FIRST_VALUE(price) OVER (\n        ORDER BY price\n    ) AS lowest_price\nFROM products;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    price,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(price) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> lowest_price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> products;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query returns each product along with the lowest price across all products. The FIRST_VALUE function, ordered by price, will always return the lowest price.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Partitioning with FIRST_VALUE<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Now, let&#8217;s use PARTITION BY to get more granular results:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    product_name,\n    category,\n    price,\n    FIRST_VALUE(price) OVER (\n        PARTITION BY category\n        ORDER BY price\n    ) AS lowest_category_price\nFROM products;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    price,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(price) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> lowest_category_price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> products;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query returns the lowest price within each category for every product.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Use Case: Sales Analysis<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s apply FIRST_VALUE to a more complex scenario. Imagine you&#8217;re analyzing sales data and want to compare each sale to the first sale of the day for each salesperson.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WITH daily_sales AS (\n    SELECT \n        sale_id,\n        salesperson_id,\n        sale_amount,\n        sale_datetime,\n        DATE(sale_datetime) AS sale_date\n    FROM sales\n)\nSELECT \n    ds.sale_id,\n    ds.salesperson_id,\n    ds.sale_amount,\n    ds.sale_datetime,\n    FIRST_VALUE(ds.sale_amount) OVER (\n        PARTITION BY ds.salesperson_id, ds.sale_date\n        ORDER BY ds.sale_datetime\n    ) AS first_sale_amount,\n    ds.sale_amount - FIRST_VALUE(ds.sale_amount) OVER (\n        PARTITION BY ds.salesperson_id, ds.sale_date\n        ORDER BY ds.sale_datetime\n    ) AS difference_from_first_sale\nFROM daily_sales ds\nORDER BY ds.salesperson_id, ds.sale_datetime;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> daily_sales <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        sale_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        salesperson_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        sale_amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        sale_datetime,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">(sale_datetime) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> sale_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_id<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">salesperson_id<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_amount<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_datetime<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_amount<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">salesperson_id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_datetime<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> first_sale_amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_amount<\/span><span style=\"color: #ABB2BF\"> - <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_amount<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">salesperson_id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_datetime<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> difference_from_first_sale<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> daily_sales ds<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">salesperson_id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_datetime<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query does several things:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>It creates a CTE (Common Table Expression) to extract the date from the sale datetime.<\/li>\n\n\n\n<li>It uses FIRST_VALUE to find the first sale amount for each salesperson on each day.<\/li>\n\n\n\n<li>It calculates the difference between each sale and the first sale of the day.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">This analysis could help identify patterns in sales performance throughout the day.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">FIRST_VALUE vs. MIN<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">You might wonder why we use FIRST_VALUE when we could use MIN to find the lowest value. Here&#8217;s an example that illustrates the difference:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    product_name,\n    category,\n    price,\n    FIRST_VALUE(product_name) OVER (\n        PARTITION BY category\n        ORDER BY price\n    ) AS cheapest_product,\n    MIN(price) OVER (\n        PARTITION BY category\n    ) AS lowest_price\nFROM products;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    price,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(product_name) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> cheapest_product,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">MIN<\/span><span style=\"color: #ABB2BF\">(price) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> lowest_price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> products;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">In this query, FIRST_VALUE allows us to get the name of the cheapest product in each category, while MIN only gives us the lowest price. FIRST_VALUE is more flexible, allowing you to return any column value based on the ordering of another column.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong style=\"background-color: rgb(69, 136, 216); color: rgb(241, 235, 218); font-size: calc(12px + 0.390625vw); font-weight: bold;\">SQL FIRST_VALUE with a Rapid Database Builder<\/strong><\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When working with SQL, executing efficient queries is straightforward, but sometimes more complex operations, like retrieving the first value in a partitioned dataset using the <code>FIRST_VALUE<\/code> function, require extra attention.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is where rapid database builders like Five come into play. Five provides a MySQL database for your application and generates an automatic UI, making it easier to interact with and manage your data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five allows you to create interactive forms, tables, dynamic charts, comprehensive PDF reports, and dashboards that are automatically generated based on your database schema. This makes it easier to visualize and analyze the results of queries, including those utilizing advanced window functions like <code>FIRST_VALUE<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Additionally, Five lets you write custom JavaScript and TypeScript functions, providing the flexibility to implement custom business logic that can interact with your data. This means you can incorporate SQL features like <code>FIRST_VALUE<\/code> into your application logic.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once your application is ready, Five makes deployment easy with just a few clicks, allowing you to deploy your MySQL-based application to a secure, scalable cloud infrastructure. This lets you focus on development while Five handles the complexities of cloud deployment and infrastructure management.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re serious about using SQL efficiently, give Five a try.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Build Your Database In 3 Steps<\/strong><br \/><span style=\"font-size: 14pt;\">Start Developing Today<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\" data-wplink-edit=\"true\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br \/><\/a><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Handling NULL Values<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">FIRST_VALUE can behave unexpectedly with NULL values. By default, NULL values are considered the lowest in the ordering. Here&#8217;s how to handle them:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    product_name,\n    category,\n    price,\n    FIRST_VALUE(product_name) OVER (\n        PARTITION BY category\n        ORDER BY \n            CASE WHEN price IS NULL THEN 1 ELSE 0 END,\n            price\n    ) AS cheapest_product\nFROM products;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    price,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(product_name) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> price <\/span><span style=\"color: #C678DD\">IS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> cheapest_product<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> products;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query ensures that NULL prices are considered last in the ordering.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">FIRST_VALUE with IGNORE NULLS<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Some SQL implementations support the IGNORE NULLS option with FIRST_VALUE:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    product_name,\n    category,\n    price,\n    FIRST_VALUE(price IGNORE NULLS) OVER (\n        PARTITION BY category\n        ORDER BY price\n    ) AS lowest_non_null_price\nFROM products;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    price,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(price <\/span><span style=\"color: #C678DD\">IGNORE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULLS<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> lowest_non_null_price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> products;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query returns the lowest non-NULL price for each category.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Combining FIRST_VALUE with Other Window Functions<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">FIRST_VALUE becomes even more useful when combined with other window functions. Here&#8217;s an example that ranks products within their category and shows the price difference from the cheapest product:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    product_name,\n    category,\n    price,\n    FIRST_VALUE(price) OVER (\n        PARTITION BY category\n        ORDER BY price\n    ) AS lowest_category_price,\n    price - FIRST_VALUE(price) OVER (\n        PARTITION BY category\n        ORDER BY price\n    ) AS price_difference,\n    RANK() OVER (\n        PARTITION BY category\n        ORDER BY price\n    ) AS price_rank\nFROM products;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    price,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(price) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> lowest_category_price,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    price - <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(price) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> price_difference,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">RANK<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> price_rank<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> products;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query provides a comprehensive view of product pricing within each category.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Using FIRST_VALUE for Time Series Analysis<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">FIRST_VALUE is particularly useful for time series analysis. Let&#8217;s look at an example involving stock prices:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WITH daily_stocks AS (\n    SELECT \n        stock_symbol,\n        DATE(price_datetime) AS price_date,\n        closing_price\n    FROM stock_prices\n)\nSELECT \n    ds.stock_symbol,\n    ds.price_date,\n    ds.closing_price,\n    FIRST_VALUE(ds.closing_price) OVER (\n        PARTITION BY ds.stock_symbol\n        ORDER BY ds.price_date\n        ROWS BETWEEN 30 PRECEDING AND CURRENT ROW\n    ) AS price_30_days_ago,\n    ds.closing_price - FIRST_VALUE(ds.closing_price) OVER (\n        PARTITION BY ds.stock_symbol\n        ORDER BY ds.price_date\n        ROWS BETWEEN 30 PRECEDING AND CURRENT ROW\n    ) AS price_change_30_days\nFROM daily_stocks ds\nORDER BY ds.stock_symbol, ds.price_date;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> daily_stocks <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        stock_symbol,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">(price_datetime) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> price_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        closing_price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> stock_prices<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">stock_symbol<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">price_date<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">closing_price<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">closing_price<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">stock_symbol<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">price_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ROWS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BETWEEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">30<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRECEDING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> CURRENT <\/span><span style=\"color: #C678DD\">ROW<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> price_30_days_ago,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">closing_price<\/span><span style=\"color: #ABB2BF\"> - <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">closing_price<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">stock_symbol<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">price_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ROWS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BETWEEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">30<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRECEDING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> CURRENT <\/span><span style=\"color: #C678DD\">ROW<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> price_change_30_days<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> daily_stocks ds<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">stock_symbol<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">ds<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">price_date<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query calculates the price change over a 30-day rolling window for each stock. The ROWS BETWEEN clause defines the window frame, allowing us to look back 30 days from each current row.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Performance Considerations<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While FIRST_VALUE is a useful function, it can impact query performance, especially on large datasets. Here are some tips to optimize its use:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Indexing: Ensure that columns used in the PARTITION BY and ORDER BY clauses are properly indexed.<\/li>\n\n\n\n<li>Materialized Views: For frequently run queries, consider creating a materialized view:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>CREATE MATERIALIZED VIEW product_price_analysis AS\nSELECT \n    product_name,\n    category,\n    price,\n    FIRST_VALUE(price) OVER (\n        PARTITION BY category\n        ORDER BY price\n    ) AS lowest_category_price\nFROM products;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> MATERIALIZED VIEW product_price_analysis <\/span><span style=\"color: #C678DD\">AS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    price,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(price) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> lowest_category_price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> products;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>Limit the window size: If you&#8217;re dealing with time-series data, consider limiting the window size to improve performance:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>FIRST_VALUE(price) OVER (\n    PARTITION BY category\n    ORDER BY sale_date\n    ROWS BETWEEN 365 PRECEDING AND CURRENT ROW\n) AS lowest_price_last_year<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #56B6C2\">FIRST_VALUE<\/span><span style=\"color: #ABB2BF\">(price) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> sale_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">ROWS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BETWEEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">365<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRECEDING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> CURRENT <\/span><span style=\"color: #C678DD\">ROW<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> lowest_price_last_year<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The SQL FIRST_VALUE function is a versatile tool for data analysis. It allows you to retrieve specific values from ordered sets, enabling complex comparisons and time-series analysis. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">By understanding how to use FIRST_VALUE effectively, you can unlock new insights from your data and enhance your SQL queries.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Key takeaways:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use FIRST_VALUE to retrieve the first value in an ordered set of data.<\/li>\n\n\n\n<li>Combine FIRST_VALUE with PARTITION BY for granular analysis within groups.<\/li>\n\n\n\n<li>FIRST_VALUE is more flexible than aggregate functions like MIN when you need to return related column values.<\/li>\n\n\n\n<li>Be mindful of how NULL values are handled in your FIRST_VALUE queries.<\/li>\n\n\n\n<li>Combine FIRST_VALUE with other window functions for comprehensive data analysis.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Breakdown of SQL FIRST_VALUE The SQL FIRST_VALUE function is a window function that returns the first value in an ordered set of values. It&#8217;s a tool for data analysis, allowing you to retrieve specific values from a group of rows based on a particular ordering. Let&#8217;s dive into how FIRST_VALUE works and explore its practical [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4172,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4162","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-FIRST_VALUE-Practical-Guide.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-FIRST_VALUE-Practical-Guide-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-FIRST_VALUE-Practical-Guide-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-FIRST_VALUE-Practical-Guide-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-FIRST_VALUE-Practical-Guide-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-FIRST_VALUE-Practical-Guide.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-FIRST_VALUE-Practical-Guide.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"Breakdown of SQL FIRST_VALUE The SQL FIRST_VALUE function is a window function that returns the first value in an ordered set of values. It&#8217;s a tool for data analysis, allowing you to retrieve specific values from a group of rows based on a particular ordering. Let&#8217;s dive into how FIRST_VALUE works and explore its practical&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4162","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4162"}],"version-history":[{"count":1,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4162\/revisions"}],"predecessor-version":[{"id":17826,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4162\/revisions\/17826"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4172"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}