No Subject

["Risman, Mark" <mrisman@PAINEWEBBER.COM>]

                Deborah,

                How about something like this:

                select zip_town, zip_state, count(station_id), station_id from
                us_weatherzip_code a, us_weather_zip
                where zip_code = user_zip and lower(zip_town)='san diego'
                group by zip_town, zip_state, station_id
                having count(station_id) > all(select count(*) from
                        us_weatherzip_code b, us_weather_zip
                where b.zip_code = user_zip and lower(zip_town)='san diego'
                              and b.station_id != a.station_id
                         group by b.station_id)
                order by zip_town, zip_state, count(station_id) desc

                this is assuming that the station_id field is in the 
us_weatherzip_code table; if not, try giving the two station_id tables 
referenced the "a" and "b" aliases.  There are probably faster ways to do this 
if you use multiple queries or if you can take only the first row of the query 
you already have, or something along those lines.

                Hope this helps,
                - Mark


                -----Original Message-----
                From:   Deborah DeCarvalho [mailto:ddecarvalho@IBELONG.COM]
                Sent:   Monday, September 25, 2000 11:41 AM
                To:     SQR-USERS@list.iex.net
                Subject:

                Hi all :-)
                Need some SQL help....
                Please see statement below - and data it returns.
                I only want the max row returned - not all 6 -
                How can I do that? I'm thinking it must be a subselect... but I 
just can't
                get it.
                This is kind of mission critical today..
                Thanks !
                Deb

                spool weather_stations

                set pages 5000
                set echo on

                column zip_town format a20
                column zip_state format a2
                column station_id format a36

                select zip_town, zip_state, count(station_id), station_id from
                us_weatherzip_code, us_weather_zip
                where zip_code = user_zip and lower(zip_town)='san diego'
                group by zip_town, zip_state, station_id
                order by zip_town, zip_state, count(station_id) desc

                returns

                ZIP_TOWN             ZI COUNT(STATION_ID) STATION_ID
                -------------------- -- ----------------- 
----------------------------------
                --
                SAN DIEGO            CA                71 
accuweather.us.ca.san_diego
                SAN DIEGO            CA                 3 
accuweather.us.ca.chula_vista
                SAN DIEGO            CA                 3 
accuweather.us.ca.el_cajon
                SAN DIEGO            CA                 3 
accuweather.us.ca.escondido
                SAN DIEGO            CA                 1 
accuweather.us.ca.vista
                SAN DIEGO            TX                 1 
accuweather.us.tx.corpus_christi

                6 rows selected.

                SQL>