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?
 
baretreeConnect With a Mentor Commented:
you can count the distinct values for each column


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

Open in new window

0
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.