how to count the number of times a value increase in a table

Hello all,
I have a table  called tblmycount  that is defined as shown below. I need to know how many times the rank value reaches a new high value for a given sku.


declare @tblmycount table(SKU VARCHAR(35), RANK INT)

insert into @tblmycount (SKU,  RANK)
values  ('a1', 1)
insert into @tblmycount (SKU,  RANK)
values  ('a1', 1)
insert into @tblmycount (SKU,  RANK)
values  ('b1', 2)
insert into @tblmycount (SKU,  RANK)
values  ('a1', 1)
insert into @tblmycount (SKU,  RANK)
values  (b1', 2)
insert into @tblmycount (SKU,  RANK)
values  ('a1', 2)
insert into @tblmycount (SKU,  RANK)
values  ('b1', 3)
insert into @tblmycount (SKU,  RANK)
values  ('a1', 1)


I would expect the query to return  a1's high value increased 1 time and b1's high value increased 2 times.

thanks in advance for the help, The sooner the better.
UseeMeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

baretreeCommented:
you can count the distinct values for each column


select 	count(distinct(RANK)), SKU
from 	@tblmycount
group 
by      SKU

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UseeMeAuthor Commented:
Perfect. I was hoping it would be something easy. thanks for the quick response
0
Anthony PerkinsCommented:
>>Perfect. I was hoping it would be something easy<<
Then I suggest you try it.  This is the output I get using that solution:
2      a1
2      b1

I thought you said:
"I would expect the query to return  a1's high value increased 1 time and b1's high value increased 2 times."
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

UseeMeAuthor Commented:
Yes, that is what I expected but my expectations were not correct for the table data I provided.
The solution does provide how many times the rank value changes for a given sku, but it does not  satisfy the "how many times the rank value reaches a new high " portion  of the question  in all cases.  As you could have 3,1,4 and it would count 3 changes when the only increase in the high value was from 3-4. But the solution worked for the data set I provided and I was in a hurry so I awarded points.

0
Anthony PerkinsCommented:
>>but it does not  satisfy the "how many times the rank value reaches a new high " portion  of the question  in all cases<<
That was what I was implying.  I was just curious if I was missing something, thanks for clarifying.
0
baretreeCommented:
oops then maybe i couldn't understand the initial point cause

a1 is 1,1,1 then 2 (increased 1 times) then 1 (didn't increase) so total is 1 times
b1 is 2, 2 then 3 (increased 1 times) so total is 1 times as well, not 2  :s

and actually when i tested it it failed in one of the inserts (i know, it had just a missing quotation mark) but i thought the b1 increased 2 times was just as well a mistype :s

did you find the correct query? i thought that was what you're looking for ... sorry :(
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.