Multiple ranks per record based on different fields.

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...

"Looney" wrote in message
...
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.

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

"Looney" wrote
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.

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

Thanks for your response, I will give it a shot.

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.