Re: SQL group want only one of each group

[Paul Bailey <PabloMotte+Midrange@xxxxxxxxx>]

What do you want from the query? What does your query look like?

For example, to get the count of rows with the same colour:

select colour, count(*) from theFile group by colour;
(replace colour with size or style as required)

Because each colour, style and size has a unique stock number, if you want
a list of stock numbers then a group by will just give you every row
because every row will be unique.


-Paul.


On 18 August 2017 at 07:21, Booth Martin <booth@xxxxxxxxxxxx> wrote:

> Scenario: I have a product with 5 colors, 6 styles, and 7 sizes. Every
> color has some of every style and every size.  Every style has some of
> every color and every size. every size has some of every color and every
> style.  Each permutation has a unique Stock number.
>
> I want 3 lists:  group by color( 5 rows), group by style(6 rows), group by
> size(7 rows).
>
> I can get the reports, correctly grouped, but I get as many rows as there
> are records in the file.  I can not figure out how to get just the first of
> each group.
>
>
>
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-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