Multiple ranks per record based on different fields.

L

Looney

OK

I have a table of clients, the primary key is their FilerID, then Name, Address, City, State, Zip, and Volume. I need to rank their volume based on nationwide, city, state, and zip. So xyz company is 30th in the nation, 10 in TX, 5th in Austin, and 1st in 77750. There are about 30,000 records, I was able to rank them nationwide in excel, but when trying to rank by city/state/zip, the reference cells change on resorts and skew the rankings

Any help would be appreciated...
 
G

Gary Walter

Looney said:
OK,

I have a table of clients, the primary key is their FilerID, then Name, Address,
Click to expand...
City, State, Zip, and Volume. I need to rank their volume based on nationwide, city,
state, and zip. So xyz company is 30th in the nation, 10 in TX, 5th in Austin, and
1st in 77750. There are about 30,000 records, I was able to rank them nationwide in
excel, but when trying to rank by city/state/zip, the reference cells change on
resorts and skew the rankings.Hi Looney,

I think you want something like:
(untested)

SELECT
t1.[Name],
t1.Address,
t1.City,
t1.State,
t1.Zip,
t1.Volume,
(SELECT COUNT(*)
FROM yourtable as t2
WHERE t2.Volume > t1.Volume) + 1 AS NationRank,
(SELECT COUNT(*)
FROM yourtable as t3
WHERE t3.Volume > t1.Volume
AND
t3.State = t1.State ) + 1 AS StateRank,
(SELECT COUNT(*)
FROM yourtable as t4
WHERE t4.Volume > t1.Volume
AND
t4.City = t1.City ) + 1 AS CityRank,
(SELECT COUNT(*)
FROM yourtable as t5
WHERE t5.Volume > t1.Volume
AND
t5.Zip = t1.Zip ) + 1 AS ZipRank
FROM yourtable as t1


Hopefully that will get you started.

Good luck,

Gary Walter
 
G

Gary Walter

OK,

I have a table of clients, the primary key is their FilerID, then Name, Address,
Click to expand...
City, State, Zip, and Volume. I need to rank their volume based on nationwide, city,
state, and zip. So xyz company is 30th in the nation, 10 in TX, 5th in Austin, and
1st in 77750. There are about 30,000 records, I was able to rank them nationwide in
excel, but when trying to rank by city/state/zip, the reference cells change on
resorts and skew the rankings.Hi Looney,

I think you want something like:
(untested)

SELECT
t1.[Name],
t1.Address,
t1.City,
t1.State,
t1.Zip,
t1.Volume,
(SELECT COUNT(*)
FROM yourtable as t2
WHERE t2.Volume > t1.Volume) + 1 AS NationRank,
(SELECT COUNT(*)
FROM yourtable as t3
WHERE t3.Volume > t1.Volume
AND
t3.State = t1.State ) + 1 AS StateRank,
(SELECT COUNT(*)
FROM yourtable as t4
WHERE t4.Volume > t1.Volume
AND
t4.City = t1.City ) + 1 AS CityRank,
(SELECT COUNT(*)
FROM yourtable as t5
WHERE t5.Volume > t1.Volume
AND
t5.Zip = t1.Zip ) + 1 AS ZipRank
FROM yourtable as t1


Hopefully that will get you started.

Good luck,

Gary Walter
 
L

Looney

Gary, you rock

That did the trick alright. And now that I have an idea of what to do I can apply it to several other queries I need to do. I really appreciate your help.
 
Top