

Old Hand
Group: General Forum Members
Last Login: Tuesday, April 14, 2015 10:30 PM
Points: 314,
Visits: 2,537


Hi,
I would like to create a ranking value based on the row value.For example consider the following sample Declare @t table(val varchar(20)) insert @t select 'test_1' union all select 'test_2' union all select 'Total' union all select 'test_1' union all select 'test_2' union all select 'test_3' union all select 'Total' union all select 'test_1' union all select 'test_2' union all select 'test_3' union all select 'test_4' union all select 'Total' union all select 'test_1' union all select 'test_2' union all select 'Total'
So what I would like is that all rows before the first instance of 'Total' should be assigned id=1 in a new column,the second instance of 'Total' should be assigned id=2 to all rows before second instance of 'Total' but after the first instance of 'Total'.Id=3 for all rows before third instance of 'Total' but to all rows before the second instance of 'Total'
This should be repeated for all the instances of 'Total'.




Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 5, 2015 3:26 PM
Points: 1,163,
Visits: 3,219


Hi
Firstly you need some way to order the rows by, so I've added the seq column to order on. Then it is basically a find the islands query. This should do the trick
Declare @t table(seq int, val varchar(20)) insert @t select 11, 'test_1' union all select 12, 'test_2' union all select 23, 'Total' union all select 34, 'test_1' union all select 35, 'test_2' union all select 36, 'test_3' union all select 47, 'Total' union all select 48, 'test_1' union all select 49, 'test_2' union all select 50, 'test_3' union all select 61, 'test_4' union all select 62, 'Total' union all select 63, 'test_1' union all select 74, 'test_2' union all select 85, 'Total'
SELECT SEQ, VAL, CASE WHEN G > T THEN T ELSE G END FROM ( SELECT SEQ, VAL, ROW_NUMBER() OVER (ORDER BY SEQ)  ROW_NUMBER() OVER (PARTITION BY LEFT(val,4) ORDER BY SEQ) + 1 G, ROW_NUMBER() OVER (PARTITION BY LEFT(val,4) ORDER BY SEQ) T FROM @t ) a ORDER BY SEQ Edit: After thinking a bit more about this, if your values between 'Total's aren't consistently prefixed the query above will not work as expected. Then following will allow for it
SELECT SEQ, VAL, CASE WHEN G > T THEN T ELSE G END, G, T FROM ( SELECT SEQ, VAL, ROW_NUMBER() OVER (ORDER BY SEQ)  ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) + 1 G, ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) T FROM @t ) a ORDER BY SEQ




SSCEnthusiastic
Group: General Forum Members
Last Login: Friday, May 15, 2015 4:51 PM
Points: 167,
Visits: 652


I'm trying to wrap my head around this and would appreciate some plainEnglish explanation if anyone feels up to it. I'm trying to work from the inside out, and am getting stuck on the "G" line:
SELECT SEQ, VAL, ROW_NUMBER() OVER (ORDER BY SEQ)  ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) + 1 G FROM @t I'm trying to see what the CASE statement is doing, specifically. But if I replace it with a "1" or "0", it doesn't seem to work the same...? E.G.
ROW_NUMBER() OVER (ORDER BY SEQ)  ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SEQ) + 1 G or
ROW_NUMBER() OVER (ORDER BY SEQ)  ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY SEQ) + 1 G
"If I had been drinking out of that toilet, I might have been killed." Ace Ventura




Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 5, 2015 3:26 PM
Points: 1,163,
Visits: 3,219


autoexcrement (11/13/2013)
I'm trying to wrap my head around this and would appreciate some plainEnglish explanation if anyone feels up to it. I'm trying to work from the inside out, and am getting stuck on the "G" line: SELECT SEQ, VAL, ROW_NUMBER() OVER (ORDER BY SEQ)  ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) + 1 G FROM @t I'm trying to see what the CASE statement is doing, specifically. But if I replace it with a "1" or "0", it doesn't seem to work the same...? E.G. ROW_NUMBER() OVER (ORDER BY SEQ)  ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SEQ) + 1 G or ROW_NUMBER() OVER (ORDER BY SEQ)  ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY SEQ) + 1 G Hi
The case statement is used to identify whether the row is a total or not. Including this in the PARTITION clause causes ROW_NUMBER function to work over each group. I picked 1 and 0 as flags, they could as easily have been 'T' and 'D'. So when we ROW_NUMBER over these groups we get 1 to 4 for the Totals (in sequence) and 1 to 11 for the non totals. When we compare the ROW_NUMBER with no partition to the ROW_NUMBER with a partition we get an increasing difference each time a Total row is encountered. This difference will be 1 less than the partitioned row number for the totals.
When you changed the PARTITION clause to a constant, you will not get the difference as this is the same as doing an unpartitioned row number.
Hope the following query will show this better by breaking out each of the components.
SELECT SEQ, VAL, isTotal, R rowNumberNoPartition, CASE WHEN isTotal = 0 then T else NULL end PartionedRowNumberNonTotal, CASE WHEN isTotal = 0 then R  T else NULL end nonTotalDifference, CASE WHEN isTotal = 0 then R  T + 1 else NULL end nonTotalDifferencePlus1, CASE WHEN isTotal = 1 then T else NULL end PartionedRowNumberTotal, CASE WHEN isTotal = 1 then T else R  T + 1 end groupID FROM ( SELECT SEQ, VAL, CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END isTotal, ROW_NUMBER() OVER (ORDER BY SEQ) R, ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) T, ROW_NUMBER() OVER (ORDER BY SEQ)  ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) + 1 G FROM @t ) a ORDER BY seq




SSCEnthusiastic
Group: General Forum Members
Last Login: Friday, May 15, 2015 4:51 PM
Points: 167,
Visits: 652


So, first of all, I really appreciate the explanation. It mostly makes sense to me. But I feel I'd have a 0% chance of recreating it to solve a similar problem myself.
I've been working on and off with SQL for many years. Can I ask, is this the kind of thinking that one "either has or doesn't have"? Or is it the kind of thinking that begins to come naturally only after being immersed in these types of problems on a daily basis for years?
I'm really wanting to get to the level where I can come up with beautiful solutions like this on my own, but it seems so far away and I just haven't found a clear path that seems to lead me closer. Would love any tips or thoughts you can offer on the subject.
Sorry if this is offtopic. Just feeling humbled and inspired by the posts on this site and really wanting to pursue this seriously somehow.
"If I had been drinking out of that toilet, I might have been killed." Ace Ventura




Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 5, 2015 3:26 PM
Points: 1,163,
Visits: 3,219


I don't think this is a case of either one does or doesn't, however being immersed in it does help. I don't do these types of things daily in my current workplace, however participating in the forums here and reading the articles has really helped up my game and improve my skills. Doing what you appear to be doing is definitely, in my opinion, a good way to gain a better understanding. If you are anything like me the understanding will not come immediately, but rather as a ... oh I see what they mean now ... moment. I find the spackle articles very good to help obtain these moments The authors of these are a lot better than me at explaining ideas and concepts than I am. While I don't actively participate in a lot of the threads here I do quite often try and work out solutions to problems that people post and see how close I can get to solutions that do get posted. The good thing about sites like this is that they expose you problems that you don't always encounter in your everyday work and these can then give you insights into problems that you you may be encountering.




SSCEnthusiastic
Group: General Forum Members
Last Login: Friday, May 15, 2015 4:51 PM
Points: 167,
Visits: 652


Thank you so much for the considered reply. I'm going to keep on trying to learn a bit every day and hope for more aha moments! Thanks to folks like you, this site is an amazing and inspiring resource for us mere mortals. :)
"If I had been drinking out of that toilet, I might have been killed." Ace Ventura




Old Hand
Group: General Forum Members
Last Login: Tuesday, April 14, 2015 10:30 PM
Points: 314,
Visits: 2,537


Appreciate the effort Micky.
It works perfect.



