Hi all,

Trying to return the city that has the most switches in market

            SELECT      SA.A_MARKET, SA.STATE, l7.[sw city], sa.switch
            FROM       A_S..SWIT_REA AS SA
                INNER JOIN L..[LG 7] AS L7
                  ON   SA.SWITCH = L7.SWITCH
             ORDER BY  SA.STATE, SA.A_MARKET, l7.[sw city]

returns this

A_MARKET        STATE            SW CITY                            SWITCH

ALASKA                 AK             AMBLER
AMBL3AKXADS1
ALASKA                 AK             ANAKTUVUK PASS      AKPSAFKXADS1
ALASKA                 AK             ANCHORAGE                 ANCR1AKXCDS1
ALASKA                 AK             ANCHORAGE
ANCGTRAKXNDS1
ALASKA                 AK             ANCHORAGE
ANCRUHAKZA04T
ALASKA                 AK             ANCHORAGE
ANCR67AKXORS1
ALASKA                 AK             ANCHORAGE
ANCRBEAKXWDS1
ALASKA                 AK             ANCHORAGE
ANWBCRAKXEDS1
ALASKA                 AK             ANCHORAGE
ANCBHRAKXRRS1
BIRMINGHAM       AL             BIRMINGHAM                 BRHMWEALFODS0
BIRMINGHAM       AL             BIRMINGHAM                 BRHMALLPWERS0
BIRMINGHAM       AL             BIRMINGHAM                 BRHMALHYMT0GT
BIRMINGHAM       AL             BIRMINGHAM                 BRHMALMMFSDS0
BIRMINGHAM       AL             BIRMINGHAM                 BRTHMA567LMTDS1
BIRMINGHAM       AL             BLOUNTSVILLE              BUJNMVLALXADS1
BIRMINGHAM       AL             BOAZ
BOAZALMWQAADS0
BIRMINGHAM       AL             BOLIGEE                          EUTWALBORS0
BIRMINGHAM       AL             CARBON HILL                CRHLALQQNMRS0
SAN FRANCISCO CA             LAFAYETTE                     LFYNTCAC11DS0
SAN FRANCISCO CA             LAKE BERRYESSA         LKBRKKCA11RS1
SAN FRANCISCO CA             LAKEPORT                      LKPTGGCA02DS1
SAN FRANCISCO CA             LARKSPUR                      LRKS58CA11DS0
SAN FRANCISCO CA             LAYTONVILLE               LYVL24CAXFDS0
SAN FRANCISCO CA             LEGGETT                         LGGT14CAXFRS4
SAN FRANCISCO CA             LIVERMORE                    LVMJRCAL11DS0
SAN FRANCISCO CA             LIVERMORE                    LVMRCROA11DS1
SAN FRANCISCO CA             LOLETA                            LOLTCASA11RS1
SAN FRANCISCO CA             LOS ALTOS                     LSATCA11DSMBB0
SAN FRANCISCO CA             LOS GATOS                     LSGTCAXA5YY35E
SAN FRANCISCO CA             LOS GATOS                     LSGWTCAXZF35K
SAN FRANCISCO CA             LOS GATOS                     LSGSATCAXFDS0

I want to group by  A_MARKET,  SA.STATE
                   And return the city that has the most switches which I
guess i will have to include in the group by

               SELECT      SA.A_MARKET, SA.STATE, COUNT(sa.switch) as total
                   FROM       A_S..SWIT_REA AS SA
                   INNER JOIN L..[LG 7] AS L7
                  ON   SA.SWITCH = L7.SWITCH
                       GROUP BY SA.A_MARKET, SA.STATE
                       ORDER BY  SA.STATE, SA.A_MARKET

I can group by the first 2 but not sure how to return the city with the max
count of switches?

thanks
gv

This is untested, and I might not understand the relationship between
the two tables properly, but something like the following could be
worth a try:

SELECT SA.A_MARKET, SA.STATE, l7.[sw city] as City,
       COUNT(sa.switch) as total
  FROM A_S..SWIT_REA AS SA
  JOIN L..[LG 7] AS L7
    ON SA.SWITCH = L7.SWITCH
WHERE l7.[sw city] =
       (SELECT TOP 1  X.[sw city]
          FROM L..[LG 7] as X
         WHERE SA.SWITCH = X.SWITCH
         GROUP BY X.[sw city]
         ORDER BY COUNT(X.SWITCH) DESC)
GROUP BY SA.A_MARKET, SA.STATE, l7.[sw city]
ORDER BY  SA.STATE, SA.A_MARKET

Roy Harvey
Beacon Falls, CT

Show quoteHide quote

Thanks so much for your help Roy.

I don't need every city returned just the one with the highest amount of
switches.


thanks
gv


Show quoteHide quote

That was what the WHERE clause was intended to accomplish.  I take it
that it did not work?  Perhaps I did not understand your input tables
properly.  Could you post more information on them?

Roy Harvey
Beacon Falls, CT

Perhaps I found and fixed my mistake:

SELECT SA.A_MARKET, SA.STATE, l7.[sw city] as City,
       COUNT(sa.switch) as total
  FROM A_S..SWIT_REA AS SA
  JOIN L..[LG 7] AS L7
    ON SA.SWITCH = L7.SWITCH
WHERE l7.[sw city] =
       (SELECT TOP 1  X.[sw city]
          FROM A_S..SWIT_REA AS Z
          JOIN L..[LG 7] as X
            ON Z.SWITCH = X.SWITCH
         WHERE SA.A_MARKET = Z.A_MARKET
           AND SA.STATE = Z.STATE
           AND SA.SWITCH = Z.SWITCH
         GROUP BY X.[sw city]
         ORDER BY COUNT(X.SWITCH) DESC)
GROUP BY SA.A_MARKET, SA.STATE, l7.[sw city]
ORDER BY  SA.STATE, SA.A_MARKET

Roy Harvey
Beacon Falls, CT

This is what is getting retuned:

ALASKA AK ADAK                             2
ALASKA AK AKHIOK                        1
ALASKA AK AKIACHAK                   1
ALASKA AK AKIAK                           1
ALASKA AK AKUTAN                        1
ALASKA AK ALAKANUK                  1
ALASKA AK ALITAK                          1
ALASKA AK ALLAKAKET                 1
ALASKA AK AMBLER                         1
ALASKA AK ANAKTUVUK PASS     1
ALASKA AK ANCHORAGE                9
ALASKA AK ANGOON                       1
ALASKA AK ANIAK                            1
ALASKA AK ANVIK                            1
ALASKA AK ARCTIC VILLAGE         1
PHOENIX AZ BLUE RIDGE                 1
PHOENIX AZ BUCKEYE                     1
PHOENIX AZ BULLHEAD CITY         2
PHOENIX AZ CAMP VERDE               2
PHOENIX AZ CASA GRANDE            1
PHOENIX AZ CAVE CREEK               1
PHOENIX AZ CHANDLER                  4
PHOENIX AZ CHINO VALLEY          1
PHOENIX AZ CIBECUE                      1
PHOENIX AZ CIRCLE CITY               1
PHOENIX AZ COOLIDGE                  1
.........
.........

What it should look like is this:

ALASKA AK ANCHORAGE                9
PHOENIX AZ CHANDLER                  4
.........
........

sorry for not explaing better

the 2 table that I'm joining looks somehting like this, just basic join on
the switch

CREATE TABLE [SWIT_REA ] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SWITCH] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[API_MARKET] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [LG 7] (
[SWITCH] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]



Show quoteHide quote

Show quote Hide quote Where did [sw city] go?  What are the keys?  I assume it is a column
in [LG 7] from the earlier information.  Is SWITCH the key to [LG 7],
with city an attribute?  Does every row in [LG 7] match to SWIT_REA?
Does every row in SWIT_REA match to [LG 7]?  Can a city have switches
in multiple API_MARKETs?

I seem to be having a complete mental block on this.  The approach of
a correlated subquery that returns just the top 1 within the
correlation set CAN do this, but I am missing something.

I noticed that I had failed to include city in the outer GROUP BY.  I
can't see that it would make any difference.  Did you have to make any
changes in order to test the query?  Does this one give the same
results?

SELECT SA.A_MARKET, SA.STATE, l7.[sw city] as City,
       COUNT(sa.switch) as total
  FROM A_S..SWIT_REA AS SA
  JOIN L..[LG 7] AS L7
    ON SA.SWITCH = L7.SWITCH
WHERE l7.[sw city] =
       (SELECT TOP 1 X.[sw city]
          FROM A_S..SWIT_REA AS Z
          JOIN L..[LG 7] as X
            ON Z.SWITCH = X.SWITCH
         WHERE SA.A_MARKET = Z.A_MARKET
           AND SA.STATE = Z.STATE
           AND SA.SWITCH = Z.SWITCH
         GROUP BY X.[sw city]
         ORDER BY COUNT(X.SWITCH) DESC)
GROUP BY SA.A_MARKET, SA.STATE, l7.[sw city]
ORDER BY  SA.STATE, SA.A_MARKET, l7.[sw city]

Roy

Hi Roy,

Maybe this will help, here a example you can run in query analyzer:

CREATE TABLE Test1 (
SWITCH VARCHAR(20),
Smarket VARCHAR(50),
STATE VARCHAR(30),
CITY VARCHAR(50)
)

insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('50ABBB','ALASKA',
'AK', 'ALLAKAKET')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('50AHqB','ALASKA',
'AK', 'AMBLER')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('20ABBB','ALASKA',
'AK', 'ANAKTUVUK PASS')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3ABBB','ALASKA',
'AK', 'ANCHORAGE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3ABBC','ALASKA',
'AK', 'ANCHORAGE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3ABBD','ALASKA',
'AK', 'ANCHORAGE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3ABBE','ALASKA',
'AK', 'ANCHORAGE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3ABBF','ALASKA',
'AK', 'ANCHORAGE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3ABBG','ALASKA',
'AK', 'ANCHORAGE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('zBB','ALASKA', 'AK',
'ANGOON')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('zBc','ALASKA', 'AK',
'ANIAK')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3ABvvB','ALASKA',
'AK', 'ANVIK')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3ABPUUB','ALASKA',
'AK', 'ARCTIC VILLAGE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('1ABPUUB','PHOENIX',
'AZ', 'BLUE RIDGE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('2ABPUUB','PHOENIX',
'AZ', 'BUCKEYE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('5ABPUUB','PHOENIX',
'AZ', 'BULLHEAD CITY')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('63ABPUUB','PHOENIX',
'AZ', 'CAMP VERDE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('7ABPUUB','PHOENIX',
'AZ', 'CASA GRANDE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3A9PUUB','PHOENIX',
'AZ', 'CAVE CREEK')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3AUUB','PHOENIX',
'AZ', 'CHANDLER')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3AU3B','PHOENIX',
'AZ', 'CHANDLER')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3AU4B','PHOENIX',
'AZ', 'CHANDLER')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3AU5B','PHOENIX',
'AZ', 'CHANDLER')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3AB433UB','PHOENIX',
'AZ', 'CHINO VALLEY')

insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('1UB','COLORADO
SPRINGS', 'CO', 'COLORADO CITY')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('2UB','COLORADO
SPRINGS', 'CO', 'COLORADO SPRINGS')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('3UB','COLORADO
SPRINGS', 'CO', 'COLORADO SPRINGS')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('4UB','COLORADO
SPRINGS', 'CO', 'COLORADO SPRINGS')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('5UB','COLORADO
SPRINGS', 'CO', 'COLORADO SPRINGS')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('6UB','COLORADO
SPRINGS', 'CO', 'CREEDE')
insert into Test1 (SWITCH,Smarket,STATE,CITY) Values ('7UB','COLORADO
SPRINGS', 'CO', 'FLORENCE')


Create Table Test2 (
SWITCH Varchar(20),
Fr Varchar(30),
Vcord VARCHAR(10),
Hcord VARCHAR (10))

insert into Test2 (SWITCH,Vcord,Hcord) Values ('50ABBB','200', '112')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('50AHqB','67','99')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('20ABBB','12','44')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3ABBB','33','77')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3AU3B','466','577')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3AU4B','33','233')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3AU5B','11','266')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('zBB','23','4877')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('zBc','1111','98765')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3ABvvB','555','76765')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3ABPUUB','329','45')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3ABBC','356','9')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3ABBD','9899','67')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3ABBE','877','69')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3ABBF','477','25')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3ABBG','1254','12')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('1ABPUUB','3899','3666')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('2ABPUUB','490','122')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('5UB','93','333')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('6UB','83','444')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('4UB','73','688')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('7UB','63','900')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3UB','46','255')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('2UB','43','5544')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('1UB','477','65')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('5ABPUUB','183','3788')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('63ABPUUB','5412','10999')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('7ABPUUB','4321','499')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3A9PUUB','670','6722')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3AUUB','5832','633')
insert into Test2 (SWITCH,Vcord,Hcord) Values ('3AB433UB','278','777')


Select  T1.Smarket, T1.STATE, T1.CITY,
           COUNT(T1.SWITCH) AS TOTAL
     from Test1 T1
           INNER JOIN Test2 T2
              ON T1.SWITCH = T2.SWITCH
       GROUP BY T1.Smarket, T1.STATE, T1.CITY
        ORDER BY  T1.STATE, T1.Smarket,T1.CITY

drop table test1
drop table test2


returns this:


ALASKA AK ALLAKAKET 1
ALASKA AK AMBLER 1
ALASKA AK ANAKTUVUK PASS 1
ALASKA AK ANCHORAGE 6
ALASKA AK ANGOON 1
ALASKA AK ANIAK 1
ALASKA AK ANVIK 1
ALASKA AK ARCTIC VILLAGE 1
PHOENIX AZ BLUE RIDGE 1
PHOENIX AZ BUCKEYE 1
PHOENIX AZ BULLHEAD CITY 1
PHOENIX AZ CAMP VERDE 1
PHOENIX AZ CASA GRANDE 1
PHOENIX AZ CAVE CREEK 1
PHOENIX AZ CHANDLER 4
PHOENIX AZ CHINO VALLEY 1
COLORADO SPRINGS CO COLORADO CITY 1
COLORADO SPRINGS CO COLORADO SPRINGS 4
COLORADO SPRINGS CO CREEDE 1
COLORADO SPRINGS CO FLORENCE 1


Want this:

ALASKA                           AK     ANCHORAGE                    6
PHOENIX                          AZ      CHANDLER                       4
COLORADO SPRINGS    CO     COLORADO SPRINGS     4

thanks
gerry

Yes, that certainly helped.  Turns out all I had to do was remove one
line - the one that is commented out.  The subquery should not have
matched on SWITCH, which is obvious in retrospect.

SELECT T1.Smarket, T1.STATE, T1.CITY,
       COUNT(T1.SWITCH) AS TOTAL
  FROM Test1 AS T1
  JOIN Test2 T2
    ON T1.SWITCH = T2.SWITCH
WHERE T1.CITY =
       (SELECT TOP 1 X1.CITY
          FROM Test1 AS X1
          JOIN Test2 X2
            ON X1.SWITCH = X2.SWITCH
         WHERE T1.Smarket = X1.Smarket
           AND T1.STATE = X1.STATE
--           AND T1.SWITCH = X1.SWITCH
         GROUP BY X1.CITY
         ORDER BY COUNT(X1.SWITCH) DESC)
GROUP BY T1.Smarket, T1.STATE, T1.CITY
ORDER BY T1.Smarket, T1.STATE, T1.CITY

Along the way I wrote it another way, might as well post it.

SELECT *
  FROM (SELECT T1.Smarket, T1.STATE, T1.CITY,
               COUNT(T1.SWITCH) AS TOTAL
          FROM Test1 AS T1
          JOIN Test2 T2
            ON T1.SWITCH = T2.SWITCH
         GROUP BY T1.Smarket, T1.STATE, T1.CITY) as A
WHERE A.CITY =
       (SELECT TOP 1 X1.CITY
          FROM Test1 AS X1
          JOIN Test2 X2
            ON X1.SWITCH = X2.SWITCH
         WHERE A.Smarket = X1.Smarket
           AND A.STATE = X1.STATE
         GROUP BY X1.CITY
         ORDER BY COUNT(X1.SWITCH) DESC)
ORDER BY A.Smarket, A.STATE, A.CITY

Roy Harvey
Beacon Falls, CT

Thanks Roy for your help that was it.

If I wanted add a random switch from the city that has the most switches to
the group returned
would that be additional trouble?

thanks
gv


Show quoteHide quote

Are you asking to add a column for a random switch among the multiple
switches within the city that has the most switches?  I'm not sure how
that can be done, SQL isn't good at random.  MIN and MAX would be no
problem, but random is something else.

Roy Harvey
Beacon Falls, CT

Yes
min or max is fine, it just wouldn't matter which one.

thanks
gv


Show quoteHide quote

SQL statement increase question
Help me performance optimize
Strange result
Tree table
Need help with select statement
SQL Select using parameter
Two instances of sql on one server causes problems
timeout expired
The opposite to DECLARE..
CAST and CONVERT Dates