needed SQL queries

  • Thread starter Thread starter lee
  • Start date Start date
L

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