CODEWITHSUNDEEP
sqlsql-serverselect
Eric Cheung
Eric Cheung

Reputation: 327

generate increment number in select statement using SQL server

I have a table named table1 in SQL server as follows:

colA
-------
A123
Z123
C123
B123

now I want to use one SQL statement to get the result as follows:

ID colA
--------
1  A123
2  Z123
3  C123
4  B123

The order of colA is as the order of row in the table. Do not need to sort it.

how to do that?? Thanks a lot

Upvotes: 9

Views: 85592

Answers (4)

omikes
omikes

Reputation: 8513

Here's what I always do when I need an incrementing ID without sorting the data (because my other row or rows are neither alphabetical nor chronological). Also, I despise using temp tables unless they are absolutely necessary.

SELECT ROW_NUMBER() OVER 
(
    ORDER BY (SELECT NULL)
) as ID, colA
FROM table1

Upvotes: 24

Arun Prasad E S
Arun Prasad E S

Reputation: 10115

Generating Row number when dynamic order conditions are present

select TotalCount = COUNT(U.UnitID) OVER() ,

ROW_NUMBER() over(
    order by 
     (CASE @OrderBy WHEN '1' THEN m.Title  END) ASC ,
     (CASE @OrderBy WHEN '2' THEN m.Title  END) DESC,
     (CASE @OrderBy WHEN '3' THEN Stock.Stock  END) DESC,
     (CASE @OrderBy WHEN '4' THEN Stock.Stock   END) DESC

) as RowNumber,

M.Title,U.ColorCode,U.ColorName,U.UnitID, ISNULL(Stock.Stock,0) as Stock

 from tblBuyOnlineMaster M

    inner join BuyOnlineProductUnitIn U on U.BuyOnlineID=M.BuyOnlineID 

    left join 
            ( select IT.BuyOnlineID,IT.UnitID,ISNULL(sum(IT.UnitIn),0)-ISNULL(sum(IT.UnitOut),0) as Stock 
                from [dbo].[BuyOnlineItemTransaction] IT 
                group by IT.BuyOnlineID,IT.UnitID
             ) as Stock

        on U.UnitID=Stock.UnitID


order by 
 (CASE @OrderBy WHEN '1' THEN m.Title  END) ASC ,
 (CASE @OrderBy WHEN '2' THEN m.Title  END) DESC,
 (CASE @OrderBy WHEN '3' THEN Stock.Stock  END) DESC,
 (CASE @OrderBy WHEN '4' THEN Stock.Stock   END) DESC


offset  @offsetCount rows fetch next 6 rows only

Upvotes: 2

Kaf
Kaf

Reputation: 33809

Try this using a Table variable with an Identity column.

The order of colA is as the order of row in the table. Do not need to sort it.

Fiddle demo:

declare @t table(id int identity(1,1), colA varchar(50))

--No ordering done and the same results won't be guaranteed
insert into @t select colA from Table1

select id, colA from @T

results:

| ID | COLA |
|----|------|
|  1 | A123 |
|  2 | Z123 |
|  3 | C123 |
|  4 | B123 |

Upvotes: 6

PJM
PJM

Reputation: 550

Example using ROW_NUMBER

             SELECT ROW_NUMBER() 
                OVER (ORDER BY colA)  AS Row, 
                colA
                FROM table1

Upvotes: 7

Related Questions