Grouped Row Numbers

[cliopia]

I am trying to return a RowNumber by Rank based on size,grouped by
department with an output like the following:

Dept Size RowNum
East 10 1
East 9 2
East 8 3
East 8 4
South 13 1
South 5 2
North 8 1
North 5 2
North 3 3

I'm using this query:

SELECT Department, Size,
( SELECT COUNT(*)
FROM tblDepartmentSize as b
WHERE b.department =a.department
) as RowNum
FROM tblDepartmentSize as a
ORDER BY Department, Size DESC

My results actually look like this:
Dept Size RowNum
East 10 4
East 9 4
East 8 4
East 8 4
South 13 2
South 5 2
North 8 3
North 5 3
North 3 3

Can anyone shed light on what is wrong with my query?

Thank You,
Marta

 

[Guest]

Try this --
SELECT Department, Size,
( SELECT COUNT(*) FROM tblDepartmentSize as b
WHERE b.department =a.department And b.Size >= a.Size) as RowNum
FROM tblDepartmentSize as a
ORDER BY Department, Size DESC
It will give you this due to duplicate in size --
Department Size RowNum
East 10 1
East 9 2
East 8 4
East 8 4
North 8 1
North 5 2
North 3 3
South 13 1
South 5 2

 

[jlepack]

What should your results be exactly?

 

[cliopia]

Try this --

SELECT Department, Size,
( SELECT COUNT(*) FROM tblDepartmentSize as b
WHERE b.department =a.department And b.Size >= a.Size) as RowNum
FROM tblDepartmentSize as a
ORDER BY Department, Size DESC
It will give you this due to duplicate in size --

Thank you so much, that is exactly what I was looking for. Silly me, I
missed the entire Size comparison bit!

Take Care,
Marta