Mssql Select Statement with Incremental Integer Column... Not from a Table

MSSQL Select statement with incremental integer column... not from a table

For SQL 2005 and up

SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn ) AS 'rownumber',*
    FROM YourTable

for 2000 you need to do something like this

SELECT IDENTITY(INT, 1,1) AS Rank ,VALUE
INTO #Ranks FROM YourTable WHERE 1=0

INSERT INTO #Ranks
SELECT SomeColumn  FROM YourTable
ORDER BY SomeColumn 

SELECT * FROM #Ranks
Order By Ranks

see also here Row Number

How to generate auto increment field in select query

If it is MySql you can try

SELECT @n := @n + 1 n,
       first_name, 
       last_name
  FROM table1, (SELECT @n := 0) m
 ORDER BY first_name, last_name

SQLFiddle

And for SQLServer

SELECT row_number() OVER (ORDER BY first_name, last_name) n,
       first_name, 
       last_name 
  FROM table1 

SQLFiddle

Auto increment number column based on another 2 columns in the table

You can't put a window function (like ROW_NUMBER) in a computed column, but there's a great workaround. Create a scalar-valued function with the window function inside it, and then you can use the scalar function in the computed column.

CREATE FUNCTION dbo.fnGetInvoiceNo(@InvoiceID INT)
RETURNS int
AS
BEGIN
    DECLARE @InvoiceNo INT
    ;WITH cte AS 
    (
        SELECT InvoiceID, ROW_NUMBER() OVER (PARTITION BY OrgID, CompanyID ORDER BY InvoiceID) rn
        FROM dbo.Invoices
    )
    SELECT @InvoiceNo = rn
    FROM cte
    WHERE cte.InvoiceID = @InvoiceID

    RETURN @InvoiceNo
END
GO

ALTER TABLE dbo.Invoices ADD InvoiceNo AS dbo.fnGetInvoiceNo(InvoiceID)
GO

Then:

SELECT * FROM dbo.Invoices

Returns:

InvoiceID   OrgID   CompanyID   InvoiceNo
1           1       101         1
2           1       101         2
3           1       101         3
4           2       201         1
5           2       201         2
6           2       201         3
7           2       202         1
8           2       202         2
9           2       202         3

In Mysql, how to fill a column with incremental integers?

If you don't mind the sorting of your columns you can DROP the planning_id and add it again now as Primary Key and with auto_increment like :

ALTER TABLE planning DROP COLUMN planning_id, 
ADD COLUMN planning_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, 
ADD PRIMARY KEY (planning_id);

This way your planning_id will refresh their values in an incremental order (1, 2, 3, 4 and so on).

Check MySQL AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted

Add an incremental number in a field in INSERT INTO SELECT query in SQL Server

You can use the row_number() function for this.

INSERT INTO PM_Ingrediants_Arrangements_Temp(AdminID, ArrangementID, IngrediantID, Sequence)
    SELECT @AdminID, @ArrangementID, PM_Ingrediants.ID,
            row_number() over (order by (select NULL))
    FROM PM_Ingrediants 
    WHERE PM_Ingrediants.ID IN (SELECT ID FROM GetIDsTableFromIDsList(@IngrediantsIDs)
                             )

If you want to start with the maximum already in the table then do:

INSERT INTO PM_Ingrediants_Arrangements_Temp(AdminID, ArrangementID, IngrediantID, Sequence)
    SELECT @AdminID, @ArrangementID, PM_Ingrediants.ID,
           coalesce(const.maxs, 0) + row_number() over (order by (select NULL))
    FROM PM_Ingrediants cross join
         (select max(sequence) as maxs from PM_Ingrediants_Arrangement_Temp) const
    WHERE PM_Ingrediants.ID IN (SELECT ID FROM GetIDsTableFromIDsList(@IngrediantsIDs)
                             )

Finally, you can just make the sequence column an auto-incrementing identity column. This saves the need to increment it each time:

create table PM_Ingrediants_Arrangement_Temp ( . . .
    sequence int identity(1, 1) -- and might consider making this a primary key too
    . . .
)

Adding auto increment extra column to view which is not present in table in SQL Server

You need to use row_number function

Schema:

CREATE TABLE #TAB (ID INT, MARKS INT)

INSERT INTO  #TAB
SELECT 1 ,  89   
UNION ALL
SELECT 2 ,  99  
UNION ALL
SELECT 4 ,  67 
UNION ALL
SELECT 6  , 77  

Do select the above table with Rownumber for Extra column

SELECT 
    ID, MARKS, 
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) EXTRA_COL 
FROM #TAB

The result will be

+----+-------+-----------+
| ID | MARKS | EXTRA_COL |
+----+-------+-----------+
|  1 |    89 |         1 |
|  2 |    99 |         2 |
|  4 |    67 |         3 |
|  6 |    77 |         4 |
+----+-------+-----------+

SQl Server 'Select' Query for increment new column if conditions are met

Try this answer, it's enough to apply windows function SUM with OVER clause with ORDER BY (rows between is not needed here):

declare @tbl table (id int, [Status] varchar(10));
insert into @tbl values
(1,'Good'),
(2,'Good'),
(3,'Bad'),
(4,'Good'),
(5,'Bad'),
(6,'Bad'),
(7,'Good');

select [id], 
       [status], 
       sum(case [status] when 'Good' then 1 else 0 end) over (order by id) [Value]
from @tbl

---

Related Topics