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

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