Auto Increment the Column Value by 1 Conditionally in Select Query in SQL

Conditional auto increment value in SQL server "SELECT"

You need to know how the rows are order. I suppose this is sample data and in your real scenario you can ordered the data in unique way. With your sample data I am using the following statement to order the rows:

CAST(REPLACE([Name], 'nm', '') AS INT)

There are different solutions of this (some depending on your SQL version, too). If you are using SQL Server 2012+ you can use the LAG function to find if ID value is changed from the previous and current row and then to sum this changes using OVER clause:

DECLARE @DataSource TABLE
(
    [ID] INT
   ,[Name] VARCHAR(12)
   ,[Address] VARCHAR(12)
);

INSERT INTO @DataSource ([ID], [Name], [Address])
VALUES (1, 'nm1', 'abc')
      ,(1, 'nm2', 'def')
      ,(1, 'nm3', 'ghi')
      ,(0, 'nm4', 'jkl')
      ,(0, 'nm5', 'mno')
      ,(0, 'nm6', 'pqr')
      ,(1, 'nm7', 'stu')
      ,(1, 'nm8', 'vwx')
      ,(1, 'nm9', 'yza')
      ,(0, 'nm10', 'bcd');

SELECT *
      ,SUM([change_made]) OVER (ORDER BY  CAST(REPLACE([Name], 'nm', '') AS INT))
FROM
(
    SELECT *
          ,IIF([ID] <> LAG([ID], 1, -1) OVER (ORDER BY CAST(REPLACE([Name], 'nm', '') AS INT)), 1, 0) AS [change_made]
    FROM @DataSource
) DS
ORDER BY CAST(REPLACE([Name], 'nm', '') AS INT);

Auto incremental temporary column in conditional select statement

You have to initialize @cnt to 0.

You should also do the ordering in a subquery. Otherwise, the row numbers may be assigned before ordering.

SELECT country, total_mark, (@cnt := @cnt + 1) AS rowNumber 
FROM (
    SELECT `COUNTRY`, sum(`POINT`) as total_mark
    FROM results 
    GROUP BY `COUNTRY` 
    ORDER by total_mark DESC
) AS x
CROSS JOIN (SELECT @cnt := 0) AS vars

Note that if you're using MySQL 8.x you can use the ROW_NUMBER() window function instead of a session variable.

MSSQL Can a column auto-increment only under certain circumstances

Thank you everyone for the suggestions to use a trigger (all up-voted). It turns out (as I mentioned in a comment above) an article that came up on the side-bar (SQL Server unique auto-increment column in the context of another column) shows a detailed construction of the proper INSTEAD OF INSERT trigger. The author mentions that it is "Not tested", and indeed there IS a slight error (a missing GROUP BY ParentEntityID in the WITH clause) but anybody copying the code would get an error that is obvious to fix. Probably not kosher to be correcting that post here, but the other question is 6 years old.

Increment a value depending on a condition

Simple UPDATE with WHERE condition should do the work here:

UPDATE TableName SET CCId = CCId + 1 WHERE Type = 'CreditCardPayment'

Update

You can do it automatically after INSERT statement using trigger:

CREATE TRIGGER autoIncrement
ON TableName
AFTER INSERT
AS
    UPDATE
        TableName
    SET
        CCId = -- put your 'get last of that type + 1 here
    WHERE
        Type = 'CreditCardPayment' AND Id = inserted.Id
GO

-- put your 'get last of that type + 1 here can be something like:

SELECT TOP(1) AnotherID FROM TableName WHERE Type = 'CreditCardPayment' ORDER BY AnotherID DESC

Auto increment column by condition

You need to write an BEFORE INSERT trigger to manage auto increment for project id, e.g.:

DELIMITER //

CREATE TRIGGER entry_trigger
BEFORE INSERT
   ON table FOR EACH ROW

BEGIN
   DECLARE entry int;
   SELECT IFNULL(MAX(Entry), 0) + 1 INTO entry WHERE project = NEW.project;
   SET NEW.Entry = entry;
END; //
DELIMITER ;

How do I increment a value within an SQL select statement based on values within the query

You can do this with window functions, by counting how many null values appear in prior rows:

select t.*,
    1 + count(*) filter(where exits is null) over(
        order by id
        rows between unbounded preceding and 1 preceding
    ) as group_id
from mytable t

Demo on DB Fiddle:

id | name   | age | enters | exits | group_id
-: | :----- | --: | -----: | ----: | -------:
 1 | orange |  10 |   null |     8 |        1
 2 | orange |   8 |      3 |     5 |        1
 3 | orange |   4 |      9 |  null |        1
 4 | orange |  11 |   null |     5 |        2
 5 | orange |   3 |      3 |  null |        2
 6 | lemon  |   9 |      1 |     2 |        3

Alterntively:

select t.*,
    1 - (exits is null)::int + count(*) filter(where exits is null) over(order by id) as group_id
from mytable t

Autoincrement MySQL table conditional on other column values

I think I understand what you want. "MOVE NUMBER" should be one higher every time you insert a new move for a certain player "ID" and "Level". Calling this "autoincrementing" is somewhat misleading, because MySQL already has an AUTO_INCREMENT, which is something different.

Let's first get the last "MOVE NUMBER" for a "ID" = 1 and "Level" = 1:

SELECT MAX(`MOVE NUMBER`) FROM GameScore WHERE ID = 1 AND Level = 1;

For the last results in your question this should return 2. However, this could return NULL, so we do:

SELECT IFNULL(MAX(`MOVE NUMBER`), 0) FROM GameScore WHERE ID = 1 AND Level = 1;

then it will return 0.

Now all we need to do is insert a new "VALUE", for instance 463. This goes like this:

INSERT INTO GameScore (ID, 
                       LEVEL, 
                       `MOVE NUMBER`, 
                       VALUE)
SELECT 1,
       1,
       IFNULL(MAX(`MOVE NUMBER`), 0) + 1, 
       463 
FROM GameScore 
WHERE ID = 1 AND 
      Level = 1; 

Please note that queries are untested, they are just given as examples.

How do I add an autoincrement Counter based on Conditions and conditional reset in Google-Bigquery

How to generate conditionally Numeric column in select statement

its way easier in SQLServer using Row_number:

select col1,col2, row_number() over (partition by col1 order by col1) as _KEYCOL
from align1

Demo Here

more info here: http://msdn.microsoft.com/en-us/library/ms186734.aspx

---

Related Topics