How to get sequence number?
-
I have a table with single column "Names".
Data in the table is
--------------------
Name
----
XX
YY
ZZ
I want the out put of Select Statement as
Seqno Name
---- -----
1 XX
2 YY
3 ZZ
-
There are several ways to do this. Most folks will just create a temp table with an IDENTITY column and insert the data, or add an IDENTITY column to the current table.
-
Hi
Using a correlated subquery should do the trick. It works by counting the number of values that are less than or equal to the current value (in this case in alphabetic terms, but you could use it to rank numbers or whatever) so the lowest alphabetically will only have one row that is less than or equal to itself (ie itself) - rank 1, the second row will have the first row and itself - rank two etc.
To rank high to low make the '<' a '>'.
Hope this helps.
---
set nocount on
create table #rankme (me char(2))
insert into #rankme values('XX')
insert into #rankme values('YY')
insert into #rankme values('ZZ')
select
me
, (select count(distinct me) from #rankme as r2 where (r2.me<=r.me)) as rank
from
#rankme as r
drop table #rankme
set nocount off
---
me rank
---- -----------
XX 1
YY 2
ZZ 3
-
Just read Antares reply (amazing that replies can happen while you are writing) and he is right you can use identity fields and temp tables.
-
This may not be particularly efficient, especially if the table is large, but try this:-
select (select count(*) from names n2 where n2.name <= n1.name) as SeqNo,name from names n1 order by name
-
Ian Scarlett Said:
"This may not be particularly efficient, especially if the table is large, but try this:- select (select count(*) from names n2 where n2.name <= n1.name) as SeqNo,name from names n1 order by name "
It may not be particualrly efficient but it worked, especially since the table (variable) I was working with was small.
I was creating a procedure that had to, amongst other things, generate sequence numbers for the resulting dataset. It went something like:
insert into @temorary_table
select
newid() as ID,
groupID,
calculate_last_sequence_number_from_database_table_data(groupID) as sequence_number
where
....(misc selection criteria,etc,etc)
Afterwards, I set the sequence numbers using Ian's code like:
update @temporary_table
set
sequence_number =
sequence_number +
(select count(*) from @temporary_table as t where temp.ID < t.ID and temp.groupID = t.groupID)
from
@temporary_table as temp
You've noticed that in this case, I'm keeping a separate sequence for each "groupID".
To tell the truth, I was scratching my head trying to figure out how to get it to work, before I stumbled on this post; It really helped a lot. It left me wondering though, is there a "right" or "efficient" way to accomplish this? Some way of calculating/generating the sequence numbers as the data is created?
Thanks for the heads up.
-
"It left me wondering though, is there a "right" or "efficient" way to accomplish this? Some way of calculating/generating the sequence numbers as the data is created?"
Since SQL Server is set-based, the "correct" response is that you don't need a sequential number in the database, as you can sort the data in any way you want. Of course, reality is often very different, so the "right" way to accomplish this is whatever way works for you, and for small tables, Ian's suggestion is likely the simplest as a quick and dirty method. It can get ugly very fast on larger tables though, and in those cases, the "create table with identity column/insert into table in order" method that Antares686 outlined is probably the most efficient. Some people will tell you that the "select into/order by" method is better, and from a speed perspective, they are usually right. The problem is that, although it usually does work, SQL doesn't guarantee that the results will be always sorted properly in such a situation, and in some cases won't be (optimizer choices or parallel query plans, for instance), while the insert method will always work.
-
There is very simple and fast way without using any temporary table or subquery. Steps to implement it:
1. Alter table to add an additional column for sequence numbers.
2. Update this new column with sequential numbers.
Script may look like this:
ALTER TABLE YourTable
ADD SequentialNumber int
GO
DECLARE @i int
SET @i = 0
UPDATE YourTable
SET @i = SequentialNumber = @i + 1
GO
You are done.
-
very nice!
notice the double assignment in the update.
-
leokarp said:
"There is very simple and fast way without using any temporary table or subquery. Steps to implement it:
1. Alter table to add an additional column for sequence numbers.
2. Update this new column with sequential numbers.
Script may look like this:
ALTER TABLE YourTable
ADD SequentialNumber int
GO
DECLARE @i int
SET @i = 0
UPDATE YourTable
SET @i = SequentialNumber = @i + 1
GO
You are done."
That would work... if the table i was working with had a single sequence number for the entire set. Unfortunately, the table's structure is more like:
uniqueID,
groupID,
sequenceNumber
Where there can't be any conficting groupID/sequenceNumber pairs.
The data would be something like:
ID Group Sequence
--- ----- ---------
1 Grp01 1
2 Grp01 2
3 newG 1
4 Grp01 3
5 Grp07 1
6 newG 2
etc...
The procedure to add data to this table does the following:
1. Declares a table variable to temporarily hold the data to be inserted.
2. Insert data into the table variable using a insert...select statement. Each sequence number is set to the last existing sequence number for it's group
3. Update the table variable's sequence numbers so they take the form n+1, n+2, n+3...etc.
4. Insert into the "real" table the data stored in the table variable
In this case I can't use a single variable to calculate the sequence numbers, so I resorted to the subquery method mentioned earlier in the thread. However, I was thinking about something along the lines of:
declare @sequence_numbers (groupID, sequenceNumber) --table to hold the sequence numbers
and somehow implement leokarp's solution in the form:
UPDATE YourTable
SET (@sequence_numbers.sequenceNumber where groupID = YourTable.groupID) = SequentialNumber = (@sequence_numbers.sequenceNumber where groupID = YourTable.groupID) + 1
so instead of using a single sequence number we have a set of sequence numbers; one for each groupID
Hmmm... Is there any way to access/modify a row like a variable? Or does SQL Server have array variables?
-
Jason Striker said:
"Is there any way to access/modify a row like a variable? Or does SQL Server have array variables?"
T-SQL doesn't have array variables and it is not possible to assign a result of a column modification to the column in a different table in the same update statement. Instead you can try this:
Open cursor listing all groups and perform table update based on it:
DECLARE GroupCursor INSENSITIVE CURSOR FOR
SELECTgroupID,
MAX(sequenceNumber) AS 'sequenceNumber'
FROMYourRealTable
GROUP BY groupID
/* You may want here to take care of new groups if there will be any*/
DECLARE @groupID varchar(50), /* exact data type should match what is in the table */
@i int
OPEN GroupCursor
FETCH NEXT FROM GroupCursor INTO @groupID, @i
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATEYourTempTable
SET@i = sequenceNumber = @i + 1
WHEREgroupID = @groupID
FETCH NEXT FROM GroupCursor INTO @groupID, @i
END
CLOSE GroupCursor
DEALLOCATE GroupCursor
Now you have new data numbered and ready to be inserted into 'real' table
-
Yeah, that should do the trick; repeat the same process for each groupID in the set.
I guess I was looking for a single "magic update" that did the whole thing in a single sweep, but this should work just fine.
Thanks to you and all the other guys who replied to the thread; I'm sure everybody who ever needs to work with sequence numbers will appreciate it.
-
There IS a single magical update and you sure don't need a cursor
First... here's a little test data to play with as you described...
--===== Create and populate test table in a safe area
SET NOCOUNT ON
USE TempDB
CREATE TABLE YourTable (ID INT PRIMARY KEY, [GROUP] VARCHAR(10), [Sequence] INT)
INSERT INTO YourTable
(ID, [Group])
SELECT 1,'Grp01' UNION ALL
SELECT 2,'Grp01' UNION ALL
SELECT 3,'newG' UNION ALL
SELECT 4,'Grp01' UNION ALL
SELECT 5,'Grp07' UNION ALL
SELECT 6,'newG'
--===== Add an index to control the upcoming update
-- You MUST have this "magic index" in order for this to work!!!
CREATE INDEX IX_YourTable_Group_ID ON YourTable ([GROUP],ID)
GO
And, here's the "magic update" you were looking for... I've not found one faster, yet. Do notice that this does not work without the "magic index" to go along with the "magic update"....
--===== Declare and preset some local variables
DECLARE @PrevGroup VARCHAR(10)
DECLARE @Count INT
SET @PrevGroup = ''
--===== Create the grouped running count to use as a sequence
UPDATE yt
SET @Count = [Sequence] = CASE WHEN @PrevGroup = [Group] THEN @Count + 1 ELSE 1 END,
@PrevGroup = [Group]
FROM YourTable yt WITH (INDEX(IX_YourTable_Group_ID),TABLOCK)
--===== Display the result
SELECT *
FROM YourTable
ORDER BY ID
... and, stop using reserved words for column names, heh?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The problem is that, although it usually does work, SQL doesn't guarantee that the results will be always sorted properly in such a situation, and in some cases won't be (optimizer choices or parallel query plans, for instance), while the insert method will always work.
I've been using an identity column on a sorted table for a while and just recently heard this concept from someone I work with.
Can anyone explain why/when the sort won't work properly, how could I tell if my optimization choices are setup to confound my process?
The dataset I am using has 15 million records and I am sorting on a 4 column combination to resequence.
Kindest Regards, -
Briliant!
to recap the "lesson", this is a use of a "double assignment" inside an UPDATE statement, with a CASE and a forced index.
I am sure I could use this technique in my future dev's.
thanks Jeff.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply