December 21, 2007 at 5:46 am
I have a table product with a field productid.
First,I have to calulate the count(productid).
Next, I have to calculate the nth max of (count(productid)).
can anyone help me in this regard in writing the t-sql for this problem.
Thanks ,
srikanthMVS
December 21, 2007 at 7:21 am
This sounds like homework so I will give you guidance and you should be able to get the answer.
I have a table product with a field productid.
First,I have to calulate the count(productid).
Next, I have to calculate the nth max of (count(productid)).
can anyone help me in this regard in writing the t-sql for this problem.
Ok... lookup the Count function on BOL (Books on Line)... I would be surprised if that would not either have the answer or provide you the method.
For the nth max.... do you know how to find the highest value? hint... max is a clue. See BOL
do you know how to limit the number of result rows you can get? Do you know how to establish the order in which results are returned? You will need to do those too. See BOL
Once you have that you should be able to get the 1 to n-1(th) higher values. for a given request:
if you want the 9th highest,,,, figure out how to limit the result set of a query to get the 1st through 8th values.
Let me know when you get that far....
Let me know when you get that far.
Toni
December 21, 2007 at 1:26 pm
Since you're using SQL 2005, you can use the Row_number function. makes this kinda thing very easy.
Since I also suspect homework 😉 at least until told otherwise, consider this
ROW_NUMBER() OVER (ORDER BY CountOfProduct DESC) AS OrderofProductCounts
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply