SQL Getting Non-Repeating Values [Oracle SQL*Plus]

Question

I have a table(order_t) that contains CustomerIDs and SalespersonIDs and I want to display SalespersonIDs with all the CustomerIDs associated with them.

I tried using SELECT DISTINCT

SELECT DISTINCT salespersonid, customerid
FROM order_t;

But that will repeat salespersonids.

Here is a table sample

salespersonid   customerid
--------------- ---------------
15              1
15              2
15              3
16              4
16              5
17              6

And I want to get this dataset as the result

salespersonid   customerid
--------------- ---------------
15              1
                2
                3
16              4
                5
17              6
18              4
                5
                9

This worked perfectly

BREAK ON sid
SELECT DISTINCT salespersonid sid, customerid cid
FROM ORDER_T
ORDER BY salespersonid;

Community · Accepted Answer · 2017-05-23 12:06:19Z

2

In SQL*Plus, you would do the following:

SQL> BREAK ON sid

SQL> SELECT salespersonid sid, customerid cid
       FROM order_t
      ORDER BY salespersonid;

Should give you output that looks something like this (untested):

SID     CID
-----   ------
    1        1
             2
             3
    2        1
             7
    3        1
...

EDIT:

If you wanted a single row for each salespersonid, see this Stackoverflow question on how to do that. It's not trivial to do.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Jun 9, 2011 at 16:35

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wicked Coder · Accepted Answer · 2011-06-09 16:10:31Z

0

SELECT DISTINCT will return the non-repeated combination of salespersonid and customerid.

In other words it can return same salespersonid but with different customerid.

e.g. 1,1
1,2
1,3 ...

but it wont return 1,1 | 1,2 | 1,3 again

answered Jun 9, 2011 at 16:10

Wicked Coder

1,1186 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL Getting Non-Repeating Values [Oracle SQL*Plus]

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related