CODEWITHSUNDEEP
sqlsql-servert-sql
Shad
Shad

Reputation: 1219

get Last value of a column in sql server

I want to get the last value of a column(it is not an identity column) and increment it to the value of corresponding row number generated.

Select isnull(LAST_VALUE(ColumnA) over(order by ColumnA), 0) + 
       ROW_NUMBER() OVER (ORDER BY ColumnA)
from myTable

I am calling my sp recursively hence why I thought of this logic. But it is not working.

I basically wanted, for first time 1-9 for second run (if recursively being called 2 times) 10-19 and so on.

Upvotes: 0

Views: 1382

Answers (1)

Thom A
Thom A

Reputation: 95544

Total stab in the dark, but I suspect "not working" means "returning the current row's value." Don't forget that an OVER clause defaults to the window RANGE BETWEEN PRECEDING AND CURRENT ROW when one isn't explicitly specified and there is an ORDER BY (see SELECT - OVER Clause (Transact-SQL) - ORDER BY).

ORDER BY

ORDER BY *order_by_expression* [COLLATE *collation_name*] [ASC|DESC]

Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.

  • If it is not specified, the default order is ASC and window function will use all rows in partition.
  • If it is specified, and a ROWS/RANGE is not specified, then default RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame by the functions that can accept optional ROWS/RANGE specification (for example min or max).

As you haven't defined the window, that's what your LAST_VALUE function is using. Define that you want the whole lot for the partition:

SELECT ISNULL(LAST_VALUE(ColumnA) OVER (ORDER BY ColumnA ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) + 
       ROW_NUMBER() OVER (ORDER BY ColumnA)
FROM dbo.myTable;

Though what Gordon says in their comment is the real solution:

You should be using an identity column or sequence.

This type of solution can (and will) end up suffering race conditions, as well as end up reusing "identities" when it shouldn't be.

Upvotes: 2

Related Questions