1

I am not sure where went wrong, but it seems the LAST_VALUE function is not returning the desired result. What I am trying to do is as following

Create table #temp(
   a varchar(10),
   b varchar(10),
   c datetime
   )

   insert into #temp
   Values('aa','bbb','2014-10-15 16:39:41.000'),
  ('aa','bbb','2014-10-16 06:00:04.000')

  select a,b,c,
  FIRST_VALUE(c) over (partition by a, b order by c asc) as first_date,
  LAST_VALUE(c) over (partition by a, b order by c asc) as last_date,
  row_number() over (partition by a, b order by c asc) as rn
   from #temp

The result I got is as following, which has different last value.

a | b | c | first_date | last_date | rn

aa | bbb | 2014-10-15 16:39:41.000 | 2014-10-15 16:39:41.000 | 2014-10-15 16:39:41.000 | 1

aa | bbb | 2014-10-16 06:00:04.000 | 2014-10-15 16:39:41.000 | 2014-10-16 06:00:04.000 | 2

kliest0106
  • 83
  • 8
  • 3
    possible duplicate of [SQL: Last\_Value() returns wrong result (but First\_Value() works fine)](http://stackoverflow.com/questions/15388892/sql-last-value-returns-wrong-result-but-first-value-works-fine) – Ian Preston Feb 11 '15 at 20:04

1 Answers1

0

You'll need to tell SQL Server which rows to include in the window, by default for these functions this will be "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" or in shorthand "ROWS UNBOUNDED PRECEDING", meaning include all rows from the start of the window untill the current row. So knowing this, the following would result in something you'd expected.

select a,b,c,
  FIRST_VALUE(c) over (partition by a, b order by c asc 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as first_date,
  LAST_VALUE(c) over (partition by a, b order by c asc 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_date,
  row_number() over (partition by a, b order by c asc) as rn
from #temp

PS: this gives the same result but is a bit more readable and probably faster.

select a,b,c,
  min(c) over (partition by a, b ) as first_date,
  max(c) over (partition by a, b) as last_date,
  row_number() over (partition by a, b order by c asc) as rn
 from #temp
MWillemse
  • 960
  • 5
  • 9