CODEWITHSUNDEEP
sqlsql-server
Ben
Ben

Reputation: 13

SQL Server query to get a pre-position No

I am trying to write SQL query, that will display in a new column a 'pre-position number' with values displayed based on the value on the previous position number column row.

See image for more details

I would appreciate any assistance. Thank you.

Upvotes: 0

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want to use LAG(). To get the 0, use the three argument form:

SELECT position_no,
       LAG(position_no, 1, 0) OVER (ORDER BY position_no) as pre_position_no
FROM mytable;

This assumes that you are ordering by position_no, as suggested by your sample code.

Upvotes: 0

GMB
GMB

Reputation: 222482

You seem to be looking for LAG(). For this to work, you need a column that can be used to order the data, so your RDBMS can assess which record is the previous record to the current one. Assuming that this column is called id, then:

SELECT
    id,
    position_nr,
    LAG(position_nr) OVER(ORDER BY id) pre_position_nr
FROM mytable

Upvotes: 1

Related Questions