How to Add Sequence Number for Groups in a SQL Query Without Temp Tables

How to add sequence number for groups in a SQL query without temp tables

Sample data

create table sometable([group] varchar(10), id int, somedata int)
insert sometable select 'Horses', 9, 11
insert sometable select 'chickens', 19, 121
insert sometable select 'Horses', 29, 123
insert sometable select 'chickens', 49, 124
insert sometable select 'Cows', 98, 1
insert sometable select 'Horses', 99, 2

Query

select
    Record = ROW_NUMBER() over (order by [Group], id),
    [Group],
    GroupSequence = DENSE_RANK() over (order by [Group])
from sometable

Output

Record               Group      GroupSequence
-------------------- ---------- --------------------
1                    chickens   1
2                    chickens   1
3                    Cows       2
4                    Horses     3
5                    Horses     3
6                    Horses     3

How to add sequence number for each element in a group using a SQL query without temp tables

Maybe something like this:

SELECT
    ROW_NUMBER() OVER(PARTITION BY [Group] ORDER BY Record) AS GroupSequence1,
    RANK() OVER(PARTITION BY [Group] ORDER BY Record) AS GroupSequence2,
    DENSE_RANK() OVER(PARTITION BY [Group] ORDER BY Record) AS GroupSequence3,
    Table1.Group,
    Table1.Record
FROM
    Table1

GroupSequence1, GroupSequence2 and GroupSequence3 will get you the output you want.

How to add sequence number for groups (new number if same group occurs again) in an SQL query

I think this is what you need:

WITH Src AS
(
    SELECT * FROM (VALUES 
    (1, 'Chickens'),
    (2, 'Chickens'),
    (3, 'Horses  '),
    (4, 'Cows    '),
    (5, 'Horses  '),
    (6, 'Horses  '))T(Record, [Group])
), Differentiator AS
(
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY Record) -
        RANK() OVER (PARTITION BY [Group] ORDER BY Record) Diff
    FROM Src
)
SELECT Record, [Group], DENSE_RANK() OVER (ORDER BY [Group],Diff) NewGroup
FROM Differentiator
ORDER BY Record

It produces following table:

Record   Group      NewGroup
------   -----      --------
1        Chickens   1
2        Chickens   1
3        Horses     3
4        Cows       2
5        Horses     4
6        Horses     4

Short explanation:

The key is to calculate relative positions of records in whole table and in each '[Group]' group. If records are adjacent, global number is increasing by 1 and local number is increased by 1. Thus, ROW_NUMBER() - RANK() is the same for all records. If there is a gap, there is also distortion in global numbering. It leads to different numbers generated by ROW_NUMBER() - RANK() in separated groups.

Add a sequence number for each element in a group using an Oracle SQL query

You need ROW_NUMBER

SELECT ID, VALUE, row_number() OVER (PARTITION BY ID ORDER BY value) GROUPSEQ
FROM myTable

How to get number sequence in Postgres for similar value of data in a particular column?

You are looking for row_number():

select t.*, row_number() over (partition by group order by record) as group_sequence
from t;

You can calculate this when you need it, so I see no reason to store it. However, you can update the values if you like:

update t
    set group_sequence = tt.new_group_sequence
    from (select t.*,
                 row_number() over (partition by group order by record) as new_group_sequence
          from t
         ) tt
    where tt.record = t.record;

SUM Values by Sequence Number and Group By Flag

This is a classic gaps-and-islands problem.

However, in this case the start of each island is clearly delineated by a P (or a row that is not C). So we don't need LAG for that.

We just need to assign a grouping ID for each island, which we can do using a windowed conditional COUNT. Then we simply group by that ID.

SELECT
  pv.SalesOrder,
  SalesOrderLine = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.SalesOrderLine END),
  MStockCode = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MStockCode END),
  MPrice = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MPrice END),
  MBomFlag = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MBomFlag END)
FROM (
    SELECT *,
      GroupingId = COUNT(NULLIF(t.MBomFlag, 'C')) OVER (PARTITION BY t.SalesOrder ORDER BY t.SalesOrderLine ROWS UNBOUNDED PRECEDING)
    FROM @tbl t
) pv
GROUP BY
  pv.SalesOrder,
  pv.GroupingId;

Note that NULLIF(t.MBomFlag, 'C') returns null if the flag is C, so COUNT will only count the other rows. You could also write that explicitly using COUNT(CASE WHEN t.MBomFlag = 'P' THEN 1 END)

db<>fiddle

Give a sequential number for each GROUP BY values

You can try SELECT INTO using an IDENTITY column. This will create the new temporary table #GrupTOT.

Here is a Fiddle example.

SELECT SeqNo = identity(int,1,1), --Identity column
       AY = J.AY
       BLK = J.BLK, 
       DRE = J.DRE,
       TOT = SUM(J.BORÇ) 
INTO #GrupTOT
FROM #TümDATA J 
GROUP BY J.AY, J.BLK, J.DRE;

--SELECT * FROM #GrupTOT

sql sequence by time

This is perfect for the row_number() function:

select username, date, row_number() over (partition by username order by date) as seq
from mytable;

---

Related Topics