Re: Using MIN in embedded SQL

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Another option or 2 can use a table expression, either correlated or 
nested. I'm not testing these - see if they work - I like these, because 
I feel more in control of things than if I use "fetch first row only". 
That's just me!

Correlated table expression -

with xx as (SELECT count(*) as yearcount FROM macfile WHERE ordyr 
between 2003 and 2013 GROUP BY ordyr)
select min(yearcount) into :count from xx

Nested table expression -

select min(yearcount) into :count from (SELECT count(*) as yearcount 
FROM macfile WHERE ordyr between 2003 and 2013 GROUP BY ordyr)

HTH
Vern

On 4/10/2014 11:30 AM, Michael Smith wrote:
> Great.
>
> exec sql select ordyr, count(*) into :Year, :Count from macfile
> 	where ordyr between 2003 and 2013 group by ordyr
> 	order by count(*) fetch first row only;
>
> Thank you.
>
> Michael