0

I need to assign numbers to rows based on a date. The rule is that the same number is assigned to multiple contiguous rows with the same date. When a row's date value differs from the previous row's date value, the number is incremented. The result set would look something like this (the first column would be used to determine row order):

1     7/1/2021     1
2     7/2/2021     2
3     7/2/2021     2
4     7/1/2021     3
5     7/2/2021     4

The value of the date is not what' relevant in this case. As you can see, there are repeats of the same date that get assigned different numeric values because they are not contiguous. I'm struggling to figure out how I would accomplish this.

Russ Suter
  • 33
  • 5
  • 1
    Does this answer your question? [What's the difference between RANK() and DENSE\_RANK() functions in oracle?](https://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle) – Jayasurya Satheesh Oct 14 '21 at 16:51
  • I did look at that but it produced the wrong result. DENSE_RANK() OVER (ORDER BY [TheDate]) gave me a sequence of 1,2,2,1,2 rather than 1,2,2,3,4. What am I missing? – Russ Suter Oct 14 '21 at 16:51
  • 1
    The question doesn't make sense. Rows do not have inherent ordering, so it's not clear what "contiguous" means. Voting to close. NOTE: If you added another extra column as ordering criteria, the question would make sense. – The Impaler Oct 14 '21 at 16:55
  • @RussSuter - SQL Sets are inherently unordered, if you want those 5 rows to appear in that order (and be ranked in that order) you ***must*** have another column to use in the `ORDER BY`. Do you have any other columns that allow you to enforce the ordering you've used in the question? – MatBailie Oct 14 '21 at 16:56
  • Sorry, for brevity I omitted additional data. Assume that there are other columns that determine an order and that an ORDER BY clause can be employed to enforce the order that is displayed in the example. – Russ Suter Oct 14 '21 at 16:56
  • 4
    No, asking us to ***assume*** is going to get the question closed. Please edit your question and *explicitly add the columns necessary* to enforce that ordering. Your question must be ***complete*** *(as in 'minimally complete example')* with ***all*** the information required: https://meta.stackoverflow.com/questions/366988/what-does-mcve-mean – MatBailie Oct 14 '21 at 16:58
  • Edited as requested. The data comes from a manual import file and the rows are ordered according to the line number of the csv from which the data was read. – Russ Suter Oct 14 '21 at 17:02

1 Answers1

2

This is a Gaps & Islands problem. You need to provide the extra ordering columns for the query to make sense.

If you added these, the solution would go along the lines of:

select
  d,
  1 + sum(inc) over(order by ordering_columns) as grp
from (
  select d, ordering_columns,
    case when d <> lag(d) over(order by ordering_columns) then 1 else 0 end as inc
  from t
) x
order by ordering_columns
The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • Excellent. Thank you. That did it. I was almost there. I had the inner query but was not putting the puzzle pieces together. Sorry for the poorly worded question at the beginning. – Russ Suter Oct 14 '21 at 17:36
  • For performance reasons you should always use `ROWS UNBOUNDED PRECEDING` in a running `OVER` calculation, otherwise it defaults to `RANGE`. And if `ordering_columns` is not unique then you get incorrect results if you don't do this – Charlieface Oct 14 '21 at 18:37