Re: Real life SQL Brain teaser -- MIDRANGE-L

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