CODEWITHSUNDEEP
sqlsql-serverselectauto-increment
Dizzy49
Dizzy49

Reputation: 1520

Add Auto Incremented Column to SELECT With Starting Number

I'm on SQL Server 12

I am trying to import a bunch of orders. I need to grab the last order number from the system, and start the import numbering there.

I have the following so far:

DECLARE
@maxOrders int

SELECT @maxOrders= MAX(order_number) FROM orders WHERE balance <> 0

I have a fairly large select statement with several joins and wheres. I found a bunch of examples on SO on using ROW_NUMBER() to increment, but I don't understand the OVER part.

My SELECT looks something like this:

SELECT
    (@maxOrders+ROW_NUMBER()) AS Order_number,
    'Company' AS Company_Name
    Three
    Four
    ...
    TwentyFive
    '$' AS Currency
FROM other_order
    left join bla bla on bla bla
    left join bla bla on bla bla
    left join bla bla on bla bla
WHERE 
    this=that
    that is null
    this is not null
ORDER BY po_number

I don't get the OVER part, where to put it etc, so I haven't even tried to see if I can add the @maxOrders to it.

Any help is appreciated!

Upvotes: 0

Views: 103

Answers (1)

SMor
SMor

Reputation: 2862

Below is an example. I've duplicated the row_number logic so you can see the value it computes and then how to add the "base number" to it as well. This case is very simple but not so intuitive in code because the original goal (guessing here) of the function was to generate a sequence based on a specific row order. I'm guessing that you don't care about the order of rows - so the "select 1" part acts as a constant in the ordering logic (which means the order of rows - and the assignment of a value to each row - is undefined). 

use tempdb;
set nocount on;
go

declare @startnum smallint = 55;
declare @import table (id smallint not null, descript varchar(20) not null);
insert @import (id, descript) values (22, 'zork'), (23, 'pocket'), (11, 'lint'), (101, 'chacha');

select * from @import;

select id, descript, 
  row_number() over (order by (select 1)) as row_num, 
  @startnum + row_number() over (order by (select 1)) as order_number 
from @import
order by order_number;

Upvotes: 1

Related Questions