Multiple ranks per record based on different fields.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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