check this one........
SELECT w1.CATEGORYID, w1.UNITSINSTOCK,COUNT(*) As Rank
FROM
(SELECT CATEGORYID, UNITSINSTOCK
FROM PRODUCTS
GROUP BY CATEGORYID, UNITSINSTOCK ) AS w1
INNER JOIN
(SELECT CATEGORYID, UNITSINSTOCK
FROM PRODUCTS
GROUP BY CATEGORYID, UNITSINSTOCK ) AS w2
ON w1.CATEGORYID = w2.CATEGORYID
AND w1.UNITSINSTOCK <= w2.UNITSINSTOCK
GROUP BY w1.CATEGORYID, w1.UNITSINSTOCK
HAVING COUNT(*) <=@parameter
ORDER BY w1.CATEGORYID,w1.UNITSINSTOCK DESC
Regards
Dinesh
[quoted text, click to view] therealbigcoops@gmail.com wrote:
> Hello,
>
> I have been attempting to set up a report using the ReportViewer
> control in VS2005. The report graphs the TOP X number of records based
> on certain criteria. Naturally, I modified the report with a filter
> for the TOP N results. This works, however, in the case of ties on the
> last entry, all tied instances will get graphed. In my case, this may
> be alot of entries causing the bar graph image to display unreadable
> data since there are too many result to graph.
>
> So, I attempted the following with no success since the server is SQL
> 2000 and not SQL 2005:
>
> - Using the TOP keyword in the select query does not work with a
> parameter (ie SELECT TOP @rowcount FROM...) since I am using SQL 2000.
> - I cannot use a stored procedure and use the "SET ROWCOUNT" command
> with the select query.
> - I cannot use the LIMIT keyword in SQL 2000.
>
> Can anyone come up with a solution, other than upgrading to SQL 2005?
>
> Thanks in advance,
> Andrew
