Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ranking value based on row value


Ranking value based on row value

Author
Message
Sachin Nandanwar
Sachin Nandanwar
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 2633
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'.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
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


autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 777
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:

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
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
autoexcrement (11/13/2013)
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:

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


autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 777
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 off-topic. 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
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
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.
autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 777
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 a-ha moments! Thanks to folks like you, this site is an amazing and inspiring resource for us mere mortals. Smile


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Sachin Nandanwar
Sachin Nandanwar
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 2633
Appreciate the effort Micky.

It works perfect.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search