No Subject

["Aguirre, Jose' A" <jaguir@LSUHSC.EDU>]

Try:

BEGIN-SELECT
MAX(field/s)     &MAX_ID
      move &MAX_ID to $MAX_ID
FROM PS_TABLE
END-SELECT

Thanks,

José A. Aguirre


-----Original Message-----
From: Deborah DeCarvalho [mailto: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>