{"id":8804,"date":"2019-01-30T19:17:48","date_gmt":"2019-01-30T19:17:48","guid":{"rendered":"https:\/\/dbtut.com\/?p=8804"},"modified":"2019-01-30T19:17:48","modified_gmt":"2019-01-30T19:17:48","slug":"first_value-function-in-sql-servertsql","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/","title":{"rendered":"FIRST_VALUE Function in SQL Server(TSQL)"},"content":{"rendered":"

We have mentioned the LEAD and LAG functions announced with SQL Server 2012. In this article, we will examine the FIRST_VALUE function.<\/p>\n

Now let’s look at the basic template:<\/p>\n

FIRST_VALUE ( [scalar_expression )\r\nOVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )<\/pre>\n
The FIRST_VALUE function takes a value as a parameter. This value can be either a column or an expression. Order By and Partition By on the OVER Clause affects the value to be obtained.<\/p>\n
In our examples, we used the AdventureWorks database that was create for SQL Server 2008R2.<\/p>\n
Example 1:<\/strong><\/p>\n
When we run the below query, we will successfully obtain the first order date of the customer, whose CustomerID is 29994.<\/p>\n
USE AdventureWorks2008R2;\r\nGO\r\nSELECT SalesOrderID,CustomerID,OrderDate,TotalDue,\r\nFIRST_VALUE(OrderDate) OVER (ORDER BY OrderDate) as FirstOrderDate\r\nFROM Sales.SalesOrderHeader\r\nWHERE CustomerID=29994<\/pre>\n
\"\"<\/p>\n
Example 2:<\/strong><\/p>\n
When we examine the query and the result, we group by year with PARTITION BY in the OVER Clause. Then we get the first order date for each year using the FIRST VALUE function.<\/p>\n
In this example, there are two more points to consider:<\/strong><\/p>\n
    \n
  • Partition By statement uses the YEAR function<\/li>\n
  • The value given to the First_Value function is obtained from the CONVERT function.<\/li>\n<\/ul>\n
    USE AdventureWorks2008R2;\r\nGO\r\nSELECT SalesOrderID,CustomerID,\r\nYEAR(OrderDate)as[Year],TotalDue,\r\nFIRST_VALUE(CONVERT(DATE,OrderDate))\r\nOVER (Partition BY Year(OrderDate) ORDER BY OrderDate) AS FirstOrderDate\r\nFROM Sales.SalesOrderHeader\r\nWHERE CustomerID=29994<\/pre>\n
    \"\"<\/p>\n
     <\/p>\n
    <\/div>\n
    <\/path><\/svg><\/i> \"Loading\"<\/p>\n
    <\/div>\n","protected":false},"excerpt":{"rendered":"
    We have mentioned the LEAD and LAG functions announced with SQL Server 2012. In this article, we will examine the FIRST_VALUE function. Now let’s look at the basic template: FIRST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] ) The FIRST_VALUE function takes a value as a parameter. This value can …<\/p>\n
    <\/div>\n
    <\/path><\/svg><\/i> \"Loading\"<\/p>\n
    <\/div>\n","protected":false},"author":471,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3,1596],"tags":[],"class_list":["post-8804","post","type-post","status-publish","format-standard","","category-mssql","category-tsql"],"aioseo_notices":[],"yoast_head":"\nFIRST_VALUE Function in SQL Server(TSQL) - Database Tutorials<\/title>\n<meta name=\"description\" content=\"FIRST_VALUE Function in SQL Server(TSQL)\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"FIRST_VALUE Function in SQL Server(TSQL) - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"FIRST_VALUE Function in SQL Server(TSQL)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-30T19:17:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c51f418916cc.png\" \/>\n<meta name=\"author\" content=\"Resul \u00c7AVU\u015eO\u011eLU\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Resul \u00c7AVU\u015eO\u011eLU\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/\"},\"author\":{\"name\":\"Resul \u00c7AVU\u015eO\u011eLU\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/9aa581e3b03eac5044bb6e88eb239426\"},\"headline\":\"FIRST_VALUE Function in SQL Server(TSQL)\",\"datePublished\":\"2019-01-30T19:17:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/\"},\"wordCount\":174,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c51f418916cc.png\",\"articleSection\":[\"MSSQL\",\"TSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/\",\"name\":\"FIRST_VALUE Function in SQL Server(TSQL) - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c51f418916cc.png\",\"datePublished\":\"2019-01-30T19:17:48+00:00\",\"description\":\"FIRST_VALUE Function in SQL Server(TSQL)\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c51f418916cc.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c51f418916cc.png\",\"width\":820,\"height\":477},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"FIRST_VALUE Function in SQL Server(TSQL)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/9aa581e3b03eac5044bb6e88eb239426\",\"name\":\"Resul \u00c7AVU\u015eO\u011eLU\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4dfa29e15e6b78ed6ba3235d3bc8b3c1c6ff77abbfeab200700a5671181cd2cd?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4dfa29e15e6b78ed6ba3235d3bc8b3c1c6ff77abbfeab200700a5671181cd2cd?s=96&d=mm&r=g\",\"caption\":\"Resul \u00c7AVU\u015eO\u011eLU\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/resulcavusoglu\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"FIRST_VALUE Function in SQL Server(TSQL) - Database Tutorials","description":"FIRST_VALUE Function in SQL Server(TSQL)","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/","og_locale":"en_US","og_type":"article","og_title":"FIRST_VALUE Function in SQL Server(TSQL) - Database Tutorials","og_description":"FIRST_VALUE Function in SQL Server(TSQL)","og_url":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/","og_site_name":"Database Tutorials","article_published_time":"2019-01-30T19:17:48+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c51f418916cc.png","type":"","width":"","height":""}],"author":"Resul \u00c7AVU\u015eO\u011eLU","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Resul \u00c7AVU\u015eO\u011eLU","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/"},"author":{"name":"Resul \u00c7AVU\u015eO\u011eLU","@id":"https:\/\/dbtut.com\/#\/schema\/person\/9aa581e3b03eac5044bb6e88eb239426"},"headline":"FIRST_VALUE Function in SQL Server(TSQL)","datePublished":"2019-01-30T19:17:48+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/"},"wordCount":174,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c51f418916cc.png","articleSection":["MSSQL","TSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/","url":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/","name":"FIRST_VALUE Function in SQL Server(TSQL) - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c51f418916cc.png","datePublished":"2019-01-30T19:17:48+00:00","description":"FIRST_VALUE Function in SQL Server(TSQL)","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c51f418916cc.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c51f418916cc.png","width":820,"height":477},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/30\/first_value-function-in-sql-servertsql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"FIRST_VALUE Function in SQL Server(TSQL)"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/9aa581e3b03eac5044bb6e88eb239426","name":"Resul \u00c7AVU\u015eO\u011eLU","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/4dfa29e15e6b78ed6ba3235d3bc8b3c1c6ff77abbfeab200700a5671181cd2cd?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4dfa29e15e6b78ed6ba3235d3bc8b3c1c6ff77abbfeab200700a5671181cd2cd?s=96&d=mm&r=g","caption":"Resul \u00c7AVU\u015eO\u011eLU"},"url":"https:\/\/dbtut.com\/index.php\/author\/resulcavusoglu\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/8804","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/471"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=8804"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/8804\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=8804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=8804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=8804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}