Sequentially Number Rows by Keyed Group in SQL

Sequentially number rows by keyed group in SQL?

• SQL Server
• Oracle
• Postgres
• Sybase
• MySQL 8.0+
• MariaDB 10.2+

This covers most bases.

SELECT
    CODE,
    ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY NAME) - 1 As C_NO,
    NAME
FROM
    MyTable

Add a (semi-)sequential number to rows based on group by and ID, then mark newest two items

Using variables, First you create the groups order by id and name, Then sorting those grp for id, you mark the first two.

SQL DEMO

SELECT `ID`, `NAME`, rn,
        mark <= 2 as mark       
FROM (
        SELECT `ID`, `NAME`, rn, 
               @mark := IF(@grp = rn,
                           @mark + 1,
                           IF( @grp := rn, 1, 1)                                      
                           ) as  mark
        FROM (       
                SELECT `ID`, `NAME`, 
                       @rn := IF(@name = `NAME`,
                                 @rn,
                                 IF(@name := `NAME`, @rn + 1, @rn + 1)
                                ) as rn
                FROM Table1
                CROSS JOIN (SELECT @rn := 0, @name :='') var
                ORDER BY `ID`
             ) T
        CROSS JOIN (SELECT @mark := 0, @grp := 0 ) var
        ORDER BY rn, `ID` DESC
    ) Y
ORDER BY ID    

OUTPUT

How can we find gaps in sequential numbering in MySQL?

A better answer

ConfexianMJS provided a much better answer in terms of performance.

The (not as fast as possible) answer

Here's a version that works on a table of any size (not just on 100 rows):

SELECT (t1.id + 1) as gap_starts_at,
       (SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at
FROM arrc_vouchers t1
WHERE NOT EXISTS (SELECT t2.id FROM arrc_vouchers t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL

• gap_starts_at - first id in current gap
• gap_ends_at - last id in current gap

Add column that counts each item

EDIT: Per your request I added answer for SQL Server.

SQL Server:

SELECT 
  name,
  ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) As AddedColumn
FROM YourTable

MYSQL:

SQL Fiddle Demo

SELECT 
    @row_number:=CASE
        WHEN @name = name THEN @row_number + 1
        ELSE 1
    END AS num,
    @name:=name as name
FROM
    YourTable
ORDER BY name;

SQL Server Unique Composite Key of Two Field With Second Field Auto-Increment

Ever since someone posted a similar question, I've been pondering this. The first problem is that DBs don't provide "partitionable" sequences (that would restart/remember based on different keys). The second is that the SEQUENCE objects that are provided are geared around fast access, and can't be rolled back (ie, you will get gaps). This essentially this rules out using a built-in utility... meaning we have to roll our own.

The first thing we're going to need is a table to store our sequence numbers. This can be fairly simple:

CREATE TABLE Invoice_Sequence (base CHAR(1) PRIMARY KEY CLUSTERED,
                               invoiceNumber INTEGER);

In reality the base column should be a foreign-key reference to whatever table/id defines the business(es)/entities you're issuing invoices for. In this table, you want entries to be unique per issued-entity.

Next, you want a stored proc that will take a key (base) and spit out the next number in the sequence (invoiceNumber). The set of keys necessary will vary (ie, some invoice numbers must contain the year or full date of issue), but the base form for this situation is as follows:

CREATE PROCEDURE Next_Invoice_Number @baseKey CHAR(1), 
                                     @invoiceNumber INTEGER OUTPUT 
AS MERGE INTO Invoice_Sequence Stored
              USING (VALUES (@baseKey)) Incoming(base)
                 ON Incoming.base = Stored.base
   WHEN MATCHED THEN UPDATE SET Stored.invoiceNumber = Stored.invoiceNumber + 1
   WHEN NOT MATCHED BY TARGET THEN INSERT (base) VALUES(@baseKey)
   OUTPUT INSERTED.invoiceNumber ;;

Note that:

1. You must run this in a serialized transaction
2. The transaction must be the same one that's inserting into the destination (invoice) table.

That's right, you'll still get blocking per-business when issuing invoice numbers. You can't avoid this if invoice numbers must be sequential, with no gaps - until the row is actually committed, it might be rolled back, meaning that the invoice number wouldn't have been issued.

Now, since you don't want to have to remember to call the procedure for the entry, wrap it up in a trigger:

CREATE TRIGGER Populate_Invoice_Number ON Invoice INSTEAD OF INSERT
AS 
  DECLARE @invoiceNumber INTEGER
  BEGIN
    EXEC Next_Invoice_Number Inserted.base, @invoiceNumber OUTPUT
    INSERT INTO Invoice (base, invoiceNumber) 
                VALUES (Inserted.base, @invoiceNumber)
  END

(obviously, you have more columns, including others that should be auto-populated - you'll need to fill them in)

...which you can then use by simply saying:

INSERT INTO Invoice (base) VALUES('A');

So what have we done? Mostly, all this work was about shrinking the number of rows locked by a transaction. Until this INSERT is committed, there are only two rows locked:

• The row in Invoice_Sequence maintaining the sequence number
• The row in Invoice for the new invoice.

All other rows for a particular base are free - they can be updated or queried at will (deleting information out of this kind of system tends to make accountants nervous). You probably need to decide what should happen when queries would normally include the pending invoice...

How do I auto-increment subsets?

Assuming SSMS means you are using SQL Server, you can apply a row_number window function here in a derived table (or CTE) and directly update it:

update t set Order_Value = rn 
from (
    select *, Row_Number() over(partition by group_1, group_2 order by order_basis) rn
    from t
    where Order_Value is null
)t;

Preserving ORDER BY in SELECT INTO

What for?

Point is – data in a table is not ordered. In SQL Server the intrinsic storage order of a table is that of the (if defined) clustered index.

The order in which data is inserted is basically "irrelevant". It is forgotten the moment the data is written into the table.

As such, nothing is gained, even if you get this stuff. If you need an order when dealing with data, you HAVE To put an order by clause on the select that gets it. Anything else is random - i.e. the order you et data is not determined and may change.

So it makes no sense to have a specific order on the insert as you try to achieve.

SQL 101: sets have no order.

---

Related Topics