How to get the sequence

Question

How to get the sequence

tonyarp05 61903

SSCommitted

Points: 1515
More actions
April 10, 2012 at 9:42 am

#253881

Hi,
I have an table called type.
SeqAttributeTypeTypeid
15 10
21 10
33 10
15 11
24 11
15 12
16 13
13 14
26 14
How to get the Seq Column value like above table structure.
If typeid field having the same value 1 time then seq field value should be 1
If typeid field having the same value above 1 time then seq field value should be 1+1...
Can you help me on this., how to get this in single query.
Thanks,
Antony

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

tonyarp05 61903 (4/10/2012)
Hi,
I have an table called type.
SeqAttributeTypeTypeid
15 10
21 10
33 10
15 11
24 11
15 12
16 13
13 14
26 14
How to get the Seq Column value like above table structure.
If typeid field having the same value 1 time then seq field value should be 1
If typeid field having the same value above 1 time then seq field value should be 1+1...
Can you help me on this., how to get this in single query.
Thanks,
Antony

SELECT

ROW_NUMBER() OVER (PARTITION BY Typeid ORDER BY (SELECT NULL)) -

DENSE_RANK() OVER (PARTITION BY Typeid ORDER BY (SELECT NULL))+1 AS Seq,

AttributeType, Typeid

FROM #yourTable;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Note: while cadavre's code will generate a sequence number within the query run, it's not a deterministic sequence number. As a result, rows might get different sequence numbers within the typeID on different runs of the query.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Matt Miller (#4) (4/10/2012)
Note: while cadavre's code will generate a sequence number within the query run, it's not a deterministic sequence number. As a result, rows might get different sequence numbers within the typeID on different runs of the query.

Yep. I wrote the code above on my phone whilst walking to my car on my way home otherwise I would've stressed this point.

There needs to be a way of ordering the results to determine your sequence numbers. I've not included an order so the results are not guaranteed to be the same each time as SQL Server doesn't guarantee table ordering without an order by clause.

Take a look at this article for more information on SQL ordering.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Cadavre (4/10/2012)
Matt Miller (#4) (4/10/2012)
Note: while cadavre's code will generate a sequence number within the query run, it's not a deterministic sequence number. As a result, rows might get different sequence numbers within the typeID on different runs of the query.
Yep. I wrote the code above on my phone whilst walking to my car on my way home otherwise I would've stressed this point.
There needs to be a way of ordering the results to determine your sequence numbers. I've not included an order so the results are not guaranteed to be the same each time as SQL Server doesn't guarantee table ordering without an order by clause.
Take a look at this article for more information on SQL ordering.

Whoa!!! Good find on that link! Thanks!

--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)
Intro to Tally Tables and Functions

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Jeff Moden (4/10/2012)
Cadavre (4/10/2012)
Matt Miller (#4) (4/10/2012)
Note: while cadavre's code will generate a sequence number within the query run, it's not a deterministic sequence number. As a result, rows might get different sequence numbers within the typeID on different runs of the query.
Yep. I wrote the code above on my phone whilst walking to my car on my way home otherwise I would've stressed this point.
There needs to be a way of ordering the results to determine your sequence numbers. I've not included an order so the results are not guaranteed to be the same each time as SQL Server doesn't guarantee table ordering without an order by clause.
Take a look at this article for more information on SQL ordering.
Whoa!!! Good find on that link! Thanks!

It came from Gail. I asked for evidence to help me to explain ordering in a different thread earlier on and she produced that gem.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/