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
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
datetime whose granularity is 3 ms?
Example using ROW_NUMBER
SELECT ROW_NUMBER()
OVER (ORDER BY colA) AS Row,
colA
FROM table1
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.
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 |
clustered index automatically which decides the physical order of the rows in that table and that is the reason why you can have only one clustered index per table.
SELECT on a table with a clustered index returns the results in clustered index key order. Even if the plan shows a CI scan Transaction Isolation level, SQL Server Edition and Degree of Parallelism all might mean the results aren't in that order.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
