November 13, 2013 at 8:05 am
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
November 13, 2013 at 12:01 pm
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
November 13, 2013 at 1:20 pm
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
November 13, 2013 at 2:24 pm
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 Gor
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
November 13, 2013 at 6:03 pm
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.
November 13, 2013 at 7:58 pm
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.
November 13, 2013 at 8:30 pm
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. 🙂
November 13, 2013 at 11:50 pm
Appreciate the effort Micky.
It works perfect.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply