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

Still not clear what you want to do...

If two customers have the same distinct set of type/codes, you only want to
see the first customer?

Not sure how such a list helps with your actual requirement to update...

Seems to me that you'd want amounts involved and you'd want to see if any
customers with the same distinct set of type/code have a different total?

Charles

On Mon, Nov 23, 2020 at 9:16 AM Dave <dfx1@xxxxxxxxxxxxxx> wrote:

Yes, that's it! Customer number has nothing to do with combination Id.
Imagine all my money fell out of my pocket and everyone tried to grab what
he could. One might have 1 10 cent coin, another 5 cents and 20 cents, two
others managed to pick up a 50 cents, a 20 cents and a 2 cents. That's 4
people but only 3 exact combinations of coins with the same value.

@Niels, if I add the line (4, 'T1', 'CX') to your table "a", there are now
4 distinct lists but your select statement still returns 3.

A bit more background in case anyone is wondering why I would want to do
this: we need to update a large file containing insurance garantie data.
Each line with a type and code and an amount is a component of the
garantie. Two customers with exactly the same number of lines with the same
corresponding combinations of type and code (I left out amount to make it
simpler) effectively have the same garantie for the same object insured. We
need to identify the different garanties that exist in this file in order
to update it accordingly.

Just a thought, I've never used a CLOB before, but I'm wondering if that
would do it?

On Fri, 20 Nov 2020 at 20:32, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

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