Re: Real life SQL Brain teaser

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

That's why I tend to ask my customers to try and explain in words what 
they want.  For this request, it SEEMS that Dave is asking for a list of 
all the unique type/code combinations and the first customer where they 
appear.  This is:

select min(cust), type, code from table group by type, code order by 
type, code

Which is what Mitch provided.

On 11/19/2020 9:32 AM, Charles Wilt wrote:
> if the count is immaterial...why include it?
>
> select distinct type, code
> from table
>
> But the OP isn't looking for just the list of distinct type, codes...he
> wants a list where the customers with those distinct combinations are
> "somehow" included.
>
> The "somehow" isn't very clear to me.
>
> Charles
>
>
> On Thu, Nov 19, 2020 at 8:17 AM Bob Czopek <bczopek@xxxxxxxxxxxxxxx> wrote:
>
> > Real life SQL Brain teaser (Dave)
> > Your question is how do I get distinct type and code permutations...
> >
> > Select type, code, count(*)
> >  From table
> > Group by type, code
> > Order by type, code;
> >
> > The count is immaterial, you get all type distinct type, code
> > combinations...
> >
> > ----------------------------------------------------------------------
> >
> > message: 1
> > date: Thu, 19 Nov 2020 14:30:24 +0100
> > from: Dave <dfx1@xxxxxxxxxxxxxx>
> > subject: Fwd: Real life SQL Brain teaser
> >
> > Charles,
> >
> > That?s amazing but it?s not quite there. If I select the lines where
> > rownbr = 1, I eliminate the list from customer 3, but I also lose the first
> > line from customer 2
> >
> >
> >
> > cust       type      code      ROWNBR
> >
> > 1             T1           C1           1
> >
> > 1             T3           C2           1
> >
> >
> >
> > 2             T1           C1           2
> >
> > 2             T2           C3           1
> >
> > 2             T3           C4           1
> >
> >
> >
> > 3             T1           C1           3
> >
> > 3             T2           C3           2
> >
> > 3             T3           C4           2
> >
> >
> >
> > Thanks to everyone else for trying whose solution did not work !
> >
> >
> >
> > End of MIDRANGE-L Digest, Vol 19, Issue 1670
> > ********************************************
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/midrange-l.
> >
> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> > questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link: https://amazon.midrange.com