× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.