How to Add Sequence Number for Each Element in a Group Using a SQL Query Without Temp Tables

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 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

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 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.

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;

Add a sequence number to each element in a group using python

The question is how do I sort on multiple columns of data.

One simple trick is to use the key parameter to the sorted function.

You'll be sorting by a string built from the columns of the array.

rows = ...# your source data

def date_to_sortable_string(date):
  # use datetime package to convert string to sortable date.
  pass

# Assume x[0] === patient_id and x[1] === encounter date

# Sort by patient_id and date
rows_sorted = sorted(rows, key=lambda x: "%0.5d-%s" % (x[0], date_to_sortable_string(x[1])))

for row in rows_sorted:
  print row

How can I group by date time column without taking time into consideration

Cast/Convert the values to a Date type for your group by.

GROUP BY CAST(myDateTime AS DATE)

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

Is there a way to loop through a table variable in TSQL without using a cursor?

First of all you should be absolutely sure you need to iterate through each row — set based operations will perform faster in every case I can think of and will normally use simpler code.

Depending on your data it may be possible to loop using just SELECT statements as shown below:

Declare @Id int

While (Select Count(*) From ATable Where Processed = 0) > 0
Begin
    Select Top 1 @Id = Id From ATable Where Processed = 0

    --Do some processing here

    Update ATable Set Processed = 1 Where Id = @Id 

End

Another alternative is to use a temporary table:

Select *
Into   #Temp
From   ATable

Declare @Id int

While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End

The option you should choose really depends on the structure and volume of your data.

Note: If you are using SQL Server you would be better served using:

WHILE EXISTS(SELECT * FROM #Temp)

Using COUNT will have to touch every single row in the table, the EXISTS only needs to touch the first one (see Josef's answer below).

---

Related Topics