Re: [EXTERNAL] Re: Sql select max value question

["Art Tostaine, Jr." <atostaine@xxxxxxxxx>]

Thank you.  This is working for what I need.

Art

On Wed, May 30, 2018 at 9:42 AM, Alan Shore <ashore@xxxxxxxx> wrote:

> I created a file from your first example, called STYLEPF
> Record#         Style           Class   Qty.
>        1                J4JK2205        3       17
>        2                J4JK2205        4       4
>        3                J4LW2700        3       21
>        4                J4LW2700        4       106
>        5                J4LW2701        4       31
>        6                J7GM0558        1       2
>        7                J7GM0558        4       6
>        8                J7GM0558        6       4
>        9                J7JE0101        4       5
>       10                J7RC2521        1       49
>       11                J7RC2521        4       11
>
> I then ran the following SQL
> with maxsty as
> (SELECT STY,
> max(QTY) as MaxQty FROM axslib.stylepf GROUP BY STY ORDER BY
> sty)
> select a.sty, b.cls, a.MaxQty from maxsty a join axslib.stylepf b
> on
> a.sty = b.sty and
> a.maxqty = b.qty
>
> with the following results
> STYLE           CLASS   MAXQTY
> J4JK2205        3               17
> J4LW2700        4       106
> J4LW2701        4               31
> J7GM0558        4               6
> J7JE0101        4               5
> J7RC2521        1               49
>
> Which is what I believe you are looking for
> The SQl wiill need to be tweaked where there is more than one record that
> fits the criteria
>
>
> Alan Shore
> E-mail : ASHORE@xxxxxxxx
> Phone [O] : (631) 200-5019
> Phone [C] : (631) 880-8640
> 'If you're going through hell, keep going.'
> Winston Churchill