Subject: Re: Real life SQL Brain teaser From: Charles Wilt Date: Fri, 20 Nov 2020 12:32:36 -0700 List-archive: List-post: List-subscribe: , List-unsubscribe: ,

Ok, so if a later customer has the same (exact?) distinct set of
(type,code),

then don't need that customer included?

Charles

On Fri, Nov 20, 2020 at 10:13 AM Dave <dfx1@xxxxxxxxxxxxxx> wrote:

I'm sorry everyone, what I wrote was confusing :
In my table below there are 4 customers but only 3 distinct combinations of
Type and Code.

There are more than 3 distinct combinations of type and code. There are 3
distinct lists. Customers 1 and 4 have the same list.
Niels understood correctly although it didn't work for me, I'll try again
to make sure

Niels Liisberg <nli@xxxxxxxxxxxxxxxxx> schrieb am Do., 19. Nov. 2020,
18:37:

OK - Just my understanding - in that case : min(custno) .. group by ..
approach is much cleaner of course

On Thu, Nov 19, 2020 at 6:28 PM Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>
wrote:

I don't see where he says that, Niels. He just provides this as a
result:

combination Type Code
1 T1 C1
1 T1 C2
2 T2 C3
2 T2 C4
3 T5 C1

The "combination" in this example is the customer number. I don't see
what else it could be, so perhaps Dave can chime in with words to tell
us
what's supposed to be in that first column.

On 11/19/2020 11:19 AM, Niels Liisberg wrote:
@Joe - He is not asking for the first customer id in the set, he is
asking
for the combination number..

So therefore customer number is out of the equation.

This works and gets your "combi" column right whereas the other
solutions
fake it by doing magik with the customer number.

... However ,my solution is nowhere near beautiful.

Here "a" is just your input data so for that, your just provide your
own
table :

with a ( cust , typ , code) as ( values
(1,'T1','C1'),
(1,'T1','C2'),
(2,'T2','C3'),
(2,'T2','C4'),
(3,'T5','C1'),
(4,'T1','C1'),
(4,'T1','C2')
) , b as (
select typ , code
from a
group by typ , code
) , c as (
select row_number() over () as combi , typ
from b group by typ
)
Select combi , b.typ , b.code
from b join c on b.typ = c.typ;

Gives:

Combi, Typ, Code
1 T1 C1
1 T1 C2
2 T2 C3
2 T2 C4
3 T5 C1

On Thu, Nov 19, 2020 at 5:40 PM Joe Pluta <
joepluta@xxxxxxxxxxxxxxxxx>
wrote:

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

--
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

--
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

--
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

--
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

This thread ...

Follow-Ups:

Replies:

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].