SQLServerCentral » SQL Server 2012 - T-SQL » Ranking value based on row valueInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralMon, 16 Jan 2017 21:01:52 GMT20Ranking value based on row valuehttps://www.sqlservercentral.com/Forums/FindPost1513911.aspxHi,
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'.
Wed, 13 Nov 2013 23:50:56 GMTSachin NandanwarRE: Ranking value based on row valuehttps://www.sqlservercentral.com/Forums/FindPost1514151.aspxAppreciate the effort Micky.
It works perfect.Wed, 13 Nov 2013 23:50:56 GMTSachin NandanwarRE: Ranking value based on row valuehttps://www.sqlservercentral.com/Forums/FindPost1514135.aspxThank you so much for the considered reply. I'm going to keep on trying to learn a bit every day and hope for more a-ha moments! Thanks to folks like you, this site is an amazing and inspiring resource for us mere mortals. :)Wed, 13 Nov 2013 20:30:56 GMTautoexcrementRE: Ranking value based on row valuehttps://www.sqlservercentral.com/Forums/FindPost1514134.aspxI 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.
Wed, 13 Nov 2013 19:58:16 GMTmickyTRE: Ranking value based on row valuehttps://www.sqlservercentral.com/Forums/FindPost1514121.aspxSo, 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 off-topic. Just feeling humbled and inspired by the posts on this site and really wanting to pursue this seriously somehow.Wed, 13 Nov 2013 18:03:06 GMTautoexcrementRE: Ranking value based on row valuehttps://www.sqlservercentral.com/Forums/FindPost1514067.aspx[quote][b]autoexcrement (11/13/2013)[/b][hr]I'm trying to wrap my head around this and would appreciate some plain-English explanation if anyone feels up to it. I'm trying to work from the inside out, and am getting stuck on the "G" line:
[code="sql"]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[/code]
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.
[code="sql"]ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SEQ) + 1 G[/code]
or
[code="sql"]ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY SEQ) + 1 G[/code][/quote]
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.
[code="sql"]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[/code]Wed, 13 Nov 2013 14:24:55 GMTmickyTRE: Ranking value based on row valuehttps://www.sqlservercentral.com/Forums/FindPost1514046.aspxI'm trying to wrap my head around this and would appreciate some plain-English explanation if anyone feels up to it. I'm trying to work from the inside out, and am getting stuck on the "G" line:
[code="sql"]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[/code]
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.
[code="sql"]ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SEQ) + 1 G[/code]
or
[code="sql"]ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY SEQ) + 1 G[/code]Wed, 13 Nov 2013 13:20:15 GMTautoexcrementRE: Ranking value based on row valuehttps://www.sqlservercentral.com/Forums/FindPost1514022.aspxHi
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
[code="sql"]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[/code]
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
[code="sql"]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[/code]Wed, 13 Nov 2013 12:01:37 GMTmickyT