How do I create a running count field in a View? 1

[M8tt]

I have the following view which extracts the top 10 records from a table based on their costs. The view sorts these records in descending order so the most expensive one is at the top.

What I would like to do is add a new column as a running count to the view so that the first one is 1 and the last one is 10.

Can anyone suggest how this may be achieved?

Many Thanks in advance, Matt.
----------------------
SELECT TOP 10 dbo.DIM_MATERIAL.MATERIAL, 'Top 10' AS Ranking

FROM dbo.DIM_FLEET
INNER JOIN dbo.FACT_WD ON dbo.DIM_FLEET.FLEET_KEY = dbo.FACT_WD.FLEET_KEY
INNER JOIN dbo.BRIDGE_MATERIAL ON dbo.FACT_WD.WORKDONE_KEY = dbo.BRIDGE_MATERIAL.WORKDONE_KEY INNER JOIN dbo.DIM_MATERIAL ON dbo.BRIDGE_MATERIAL.MATERIAL_KEY = dbo.DIM_MATERIAL.MATERIAL_KEY
INNER JOIN dbo.DIM_TIME_WCTC ON dbo.FACT_WD.TIME_WCTC_KEY = dbo.DIM_TIME_WCTC.TIME_WCTC_KEY

GROUP BY dbo.DIM_FLEET.FLEET_NAME, dbo.DIM_TIME_WCTC.NOTIF_YEAR, dbo.DIM_MATERIAL.MATERIAL_PRODUCT_HIERARCHY_DESC,
dbo.DIM_MATERIAL.MATERIAL

HAVING (dbo.DIM_FLEET.FLEET_NAME = N'CL390') AND (dbo.DIM_TIME_WCTC.NOTIF_YEAR = 2004) AND (dbo.DIM_MATERIAL.MATERIAL_PRODUCT_HIERARCHY_DESC = N'Repairables')

ORDER BY SUM(dbo.BRIDGE_MATERIAL.MATERIAL_COST) DESC

 

[donutman]

It's best to do that at the application level, but for accessing the rows in batches modify the approach listed in this thread to suit your needs, thread183-980264.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]