creating a grouped "sequence" number in a query (not OVER) and not rank

Database
New Member

creating a grouped "sequence" number in a query (not OVER) and not rank

What I would like to do is create a query with something like the following output

 

sequence        ID        logdate

1                      998     12-Nov-2016

1                      998     02-April-2017

2                      472     07-July-2016

3                      696     07-May-2016

3                      696     07-Oct-2016

3                      696     08-Jan-2017

etc...

 

I dont mind about the ordering of ID and logdate at this stage (but am grouping on ID), but would like to know how to achieve the sequence. Using rank() it cause the sequence to be 1,1,3,4,4,4 instead of 1,1,2,3,3,3 . row_number() gives me 1,2,1,1,2,3 which is also not what i want.

 

 

 

 

1 REPLY
Junior Contributor

Re: creating a grouped "sequence" number in a query (not OVER) and not rank

Simply switch to DENSE_RANK instead of RANK :)