Rank data

[Bill]

I have the following query that I found in another thread. It works great
except that I need to rank the data by state within region. Can someone help
me with this alteration?

SELECT HazScore,

 

[KARL DEWEY]

I need to rank the data by state within region.
It can not be done with what you supplied. You gave no information about
your data. What is supposed to be ranked?
For your request you need at least three data elements - State. Region. and
data to be ranked.

 

[Bill]

Sorry, my data does have region and state as well as Hazscore. So my real
sql statement looks like this.

SELECT HazScore, state, region

 

[KARL DEWEY]

I assume you want the results to look like this --
Region State
A Missippi 1
A Alabama 2
A Florida 3
A Georgia 4
B Washington 1
B California 2
B Oregon 3
C Ohio 1
C Illinos 2
C Indiana 3
Try this --
SELECT region, state, (SELECT COUNT(*) FROM PrimarySlideTable T1
WHERE T1.region = T.region
AND T1.HazScore > T.HazScore)+1 AS Rank
From PrimarySlideTable T
ORDER BY region, HazScore;

 

[Bill]

Thank you very much Karl. That worked GREAT! Is there any way I can store
the rank in my table?