No Subject

[Pragya Chandra <pchandra@FASTENAL.COM>]

i believe you should be able to use max(count(station_id)) in the select
statement...but don't hold me on it....
i know you can use max(length(fieldname))...so i am thinkgin this must also
be possible.



> -----Original Message-----
> From: Deborah DeCarvalho [SMTP:ddecarvalho@IBELONG.COM]
> Sent: Monday, September 25, 2000 10: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>