DENSE_RANK different sequences
-
Hi all,
I have this data:
Code register_sequence registered_time id
4001_0_3 1 08:00:00 1
4001_0_3 2 08:02:17 2
4001_0_3 3 08:03:10 3
4001_0_3 4 08:04:08 4
4001_0_3 1 08:45:00 5
4001_0_3 2 08:47:17 6
4001_0_3 3 08:48:10 7
4001_0_3 4 08:49:08 8
4001_0_3 1 08:01:00 9
4001_0_3 2 08:03:17 10
4001_0_3 3 08:04:10 11
4001_0_3 4 08:05:08 12
What I need is to add a new field that identify the different sequences with a sequential number. Such as:
Code register_sequence registered_time seq_number
4001_0_3 1 08:00:00 1
4001_0_3 2 08:02:17 1
4001_0_3 3 08:03:10 1
4001_0_3 4 08:04:08 1
4001_0_3 1 08:45:00 2
4001_0_3 2 08:47:17 2
4001_0_3 3 08:48:10 2
4001_0_3 4 08:49:08 2
4001_0_3 1 08:01:00 3
4001_0_3 2 08:03:17 3
4001_0_3 3 08:04:10 3
4001_0_3 4 08:05:08 3
Any idea ?
-
The answer is, yes... I have an idea but... you won't like it because the answer is, there is nothing in your original data to guarantee the sequence of even the original supposed sort of the data. Are there any other columns in the original rows of data that could be used to guarantee the sort of of the original data as you've presented it?
EDIT... just so that people don't think that I've lost it, the ID column that is posted in the data above wasn't in the original post. It was added afterwards.l 😀
--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) -
What differentiates as Sequence ?
Why is "4001_0_3 1 08:00:00 " newcol 1
why is "4001_0_3 1 08:45:00" newcol 2
why is "4001_0_3 1 08:01:00 " newcol 3
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi Jeff and Johan,
I edited my original post, and included an id column, there's the only thing I can add to help sort the data.
I have other fields, but none of them allow me to order correctly, nor to differenciate any sequence.
PS: I know, my data sucks, but I can do nothing.. the database came to be like that, and now I'm stuck..
-
Please complete the following VALUES section code to give us your test data to work with:
DROP TABLE IF EXISTS #SomeTab;
CREATE TABLE #SomeTab
(
Id INT NOT NULL PRIMARY KEY CLUSTERED
,Code VARCHAR(10) NOT NULL
,register_sequence SMALLINT NOT NULL
,registered_time TIME NOT NULL
);
INSERT #SomeTab
(
Id
,Code
,register_sequence
,registered_time
)
VALUES
(1, '4001_0_3', 1, '08:00:00')
,(2, '4001_0_3', 2, '08:02:17');
SELECT * FROM #SomeTab stThe absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
DENSE_RANK() OVER(ORDER BY Id - register_sequence) AS seq_number
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark Cowne.
That is the perfect solution.. and right in front of my eyes.... still, didn't figure it out.
DENSE_RANK() OVER(ORDER BY Id - register_sequence) AS seq_number
works beautifully!!
-
@vilaca ,
Thanks for the ID column but that can still be problematic. If it's created using an IDENTITY column or from a SEQUENCE object, there's no guarantee that you won't have gaps over time unless the column IS and IDENTITY column AND the data was loaded from a file in a single threaded fashion by something like BCP or BULK INSERT... or so people will tell you. It's better to be safe than sorry.
The DENSE_RANK() method that Mark Cowne posted is certainly the right way to do this but... it needs a little bullet-proofing because of the gap problem I mentioned above. We need to guarantee that something is guaranteed to have no gaps in an ever increasing fashion. The only way to do that easily is to make one on the fly in the code, thusly...
WITH cte AS
(SELECT *, Contig = ROW_NUMBER() OVER (ORDER BY ID) --Contiguous, ever-increasing
FROM #TestTable)
SELECT *
,seq_number = DENSE_RANK() OVER(ORDER BY Contig - register_sequence)
FROM cte
;Also, in the future, please submit your data in a readily consumable format. You'll get answers much more quickly and they'll be tested. Here's how I made the example to test the above. Please see the article at the first link in my signature line below for more info on the many advantages of taking that bit of extra time...
DROP TABLE IF EXISTS #TestTable
;
GO
SELECT Code = CONVERT(CHAR(8),v.Code )
,register_sequence = CONVERT(INT ,v.register_sequence)
,registered_time = CONVERT(TIME ,v.registered_time )
,id = CONVERT(INT ,v.id )
INTO #TestTable
FROM (VALUES
('4001_0_3',1,'08:00:00', 1)
,('4001_0_3',2,'08:02:17', 2)
,('4001_0_3',3,'08:03:10', 3)
,('4001_0_3',4,'08:04:08', 4)
,('4001_0_3',5,'08:04:08', 5) --Added this
,('4001_0_3',1,'08:45:00', 6)
,('4001_0_3',2,'08:47:17', 8) --Skipped an ID (it happens)
,('4001_0_3',3,'08:48:10', 9)
,('4001_0_3',4,'08:49:08',10)
,('4001_0_3',1,'08:01:00',11)
,('4001_0_3',2,'08:03:17',12)
,('4001_0_3',3,'08:04:10',13)
,('4001_0_3',4,'08:05:08',14)
)v(Code,register_sequence,registered_time,id)
;Here are the results from all that...
Since the data already supports it, here's another way to get the correct output in a guaranteed fashion that doesn't look quite as obnoxious.
SELECT *,seq_number = SUM(IIF(register_sequence=1,1,0)) OVER (ORDER BY ID)
FROM #TestTable
ORDER BY ID
;--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) - vilaca wrote:
Thanks Mark Cowne. That is the perfect solution.. and right in front of my eyes.... still, didn't figure it out.
DENSE_RANK() OVER(ORDER BY Id - register_sequence) AS seq_number
works beautifully!!
Careful now. There's no guarantee that the ID will always be perfectly contiguous. See my post above.
This is what happens with Mark's otherwise perfect code if you have either a gap ...
--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) -
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply