How to get row index in column

[Nicolas]

I explain the problem
COL0 COL1 COL2 COL3 COL4 COL5
row1 blabla 37 M X 1
row2 dgdgg 35 M X 2
row3 fefcec 20 M X 3
row4 revev 49 F X 1
row5 wcw 30 F X 2
row6 ryryy 13 F X 3
row7 ikkifg 33 C X 1
row8 lopjg 17 C X 2
row9 rgrggr 2 C X 3

Col0 and Col1 are holding basic stuff
Col2 are age (sorted)
Col3 are category like Male, Female, Child (grouped)
Col4 should be the number Im trying to get from the Select Statement where x
should read as follow: for each group in Col3 I should get 1, 2, 3 then again
1, 2, 3 etc. which is represented in COL5. Consider that has ranking or
positin.

Any idea will be great.

[Hugo Kornelis]

Hi Nicolas,

Try:

SELECT Col0, Col1, Col2, Col3,
RANK() OVER (PARTITION BY Col3 OPRDER BY Col2 DESC) AS Col4
FROM YourTable
ORDER BY Col3, Col4;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis