Re: Real life SQL Brain teaser

This works and get 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 own table 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 Wed, Nov 18, 2020 at 7:58 PM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

-- get a numbered list of customers with each distinct (type,code)
with list as (select customer, type, code, row_number() over(partition by
type, code order by customer asc) as rowNbr
from mytbl)
select customer, type, code
from list
where rowNbr = 1
order by customer, type, code;

Charles

On Wed, Nov 18, 2020 at 11:07 AM Dave <dfx1@xxxxxxxxxxxxxx> wrote:

Hi,



Before I start writing a program, I was wondering if SQL could offer an
easier solution. I've been looking at using rank and over functions, but

I

can't see it! :

In my table below there are 4 customers but only 3 distinct combinations

of

Type and Code.

Customer Type Code

1 T1 C1

1 T1 C2

2 T2 C3

2 T2 C4

3 T5 C1

4 T1 C1

4 T1 C2



From this table, can I create the following table that contains each
combination of Type and Code only once ?

combination Type Code

1 T1 C1

1 T1 C2

2 T2 C3

2 T2 C4

3 T5 C1



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