A different less elegant solution
select class from xyzfile
where style='XXXX'
and class in (select class from xyzfile
where style='XXXX'
order by qty desc) fetch first 1 rows only
Jim Franz
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Art
Tostaine, Jr.
Sent: Tuesday, May 29, 2018 10:06 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: [EXTERNAL] Sql select max value question
Thanks Rob. I made my column and file names correct and I get t1.sty not
found. I've never done WITH before. I see that it's like a create view.
with t1 as (
select sty, max(qty) as maxqty
from whso313f
group by sty)
select m.sty, m.cls, m.qty
from whso313f m
where sty=t1.sty and m.qty=t1.qty
whso313f is sty,cls,qty
If my data was like this:
sty
Thanks again.
Art
On Tue, May 29, 2018 at 9:19 PM, Rob Berendt <rob@xxxxxxxxx> wrote:
with t1 as (
select style, max(qty) as maxqty
from mytable
group by style)
select m.style, m.class, m.qty
from mytable m
where style=t1.style and m.qty=t1.qty
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
