CODEWITHSUNDEEP
sqldatabasesql-updatesybase
Ted Dietz
Ted Dietz

Reputation: 1

How do I add an incrementing number to multiple rows in a database table

I need to add line items to a table. Here is an example of what I have (on a much smaller scale):

shiptoid,lineitem
642218,0
642218,0
642218,0
3041340,0
3041077,0
3041077,0

And this is what I need

shiptoid,lineitem
642218,1
642218,2
642218,3
3041340,1
3041077,1
3041077,2

For each unique shiptoid, I need an incrementing line item number that starts at 1. I've done something similar in the past, but I can't for the life of me figure it out.

Upvotes: 0

Views: 1269

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

OLD QUERIES

For SQL Server:

SELECT ROW_NUMBER() 
        OVER (ORDER BY ShiptoID) AS Row, 
    ShiptoID
FROM YourShip

For MYSQL:

SELECT s.shiptoid, 
       @rownum := @rownum + 1 AS Row
  FROM yourship s, 
       (SELECT @rownum := 0) r

For Oracle:

SELECT ShiptoID, ROWNUM AS Row, 
    ShiptoID
FROM YourShip

UPDATED QUERIES

For MySQL: * SQLFIDDLE : ignore the other tables in the same page.

Query:

select y.shiptoID, y.row 
      from (select shiptoID,
      @row:=if(@x=shiptoID,@row,0) + 1 as row,
      @x:=shiptoID 
                from yourship
            order by shiptoID, row asc
    )as y

Results:

SHIPTOID    ROW
642218      1
642218      2
642218      3
3041077     1
3041077     2
3041340     1

For Oracle: Oracle has RANK(), DENSE_RANK() OVER PARTITION similar to MYSQL.

So we can use similar query as MYSQL in ORACLE. But the best part is, that I used ROWNUM in the ORDER BY clause ;) So to say easiest logic/syntax out of all 3

Reference : * SQLFIDDLE

Query:

SELECT shiptoID, 
DENSE_RANK() OVER 
(PARTITION BY shiptoID ORDER BY rownum) AS row
FROM yourShip
;

Results:

SHIPTOID    ROW
642218      1
642218      2
642218      3
3041077     1
3041077     2
3041340     1

Upvotes: 0

user330315
user330315

Reputation:

You didn't mention your DBMS, so this is ANSI SQL (which works with most modern DBMS):

SELECT shiptoid,
       row_number() over (partition by shiptoid) as lineitem
FROM the_table;

Upvotes: 0

James L.
James L.

Reputation: 9461

If you are using SQL Server, this will work:

declare @tbl table (shiptoid int)

insert into @tbl values (642218), (642218), (642218), (3041340), (3041077), (3041077)

select shiptoid, row_number() over (partition by shiptoid order by shiptoid) as lineitem    from @tbl

Here is what it returns:

shiptoid    lineitem
----------- --------------------
642218      1
642218      2
642218      3
3041077     1
3041077     2
3041340     1

Upvotes: 1

Related Questions