needed SQL queries

[lee]

ziplow ziphigh GA PA IL rank1 rank2
5 5 5 6 GA PA
6 9 7 8 8 GA PA
10 13
24 3 IL
43 5
65 7 6
79 5
129 5
14 34
25 26 4
27 29 3
30 54 4
35 37 2
38 43 3
44 6 4
45 5 3
46 47 6 4
48 5 3
49 6 4
50 54 2
149 5

I need to add two columns rank 1 and rank 2 to the above table. For
each combination of zip code, zones are listed below every state.
rank1 column lists the state with minimium zone and rank 2 lists the
state with next highest zone.

Can you please let me know the query which will generate the columns
rank1 and rank2 Thanks

 

[John Spencer]

Ugly table structure.

What are you going to do about two-way ties for first or second or three-way
ties for first?

Assuming NO ties, you might be able to use expressions like the following to
get the Rank1 and Rank2 results.

SELECT
IIF(NZ(GA)>=NZ(PA) AND NZ(GA)>NZ(IL),"GA",NZ(PA)>=NZ(IL),"PA","IL") As Rank1,

Switch((NZ(GA)<=NZ(PA) AND NZ(GA)<=NZ(IL))
OR (NZ(GA)>=NZ(PA) AND NZ(GA)<=NZ(IL)),"GA",
(NZ(PA)<=NZ(GA) AND NZ(PA)<=NZ(IL))
OR (NZ(PA)>=NZ(GA) AND NZ(PA)<=NZ(IL)),"PA",
(NZ(IL)<=NZ(GA) AND NZ(IL)<=NZ(PA))
OR (NZ(IL)>=NZ(GA) AND NZ(IL)<=NZ(PA)),"IL") as Rank2

FROM [YourRecordSource]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County