Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: sql question - distinct
soalvajavab1_at_yahoo.com wrote:
> How can I apply DISTINCT only to the first column in this query:
>
> SELECT DISTINCT UNIT_ID, BFP_NUMBER, METER_NUMBER
> FROM table_1
>
> It applies it to all 3 fields now. Pls advise.
I am having trouble understanding what you are trying to do. Distinct makes certain that any combination of UNIT_ID, BFP_NUMBER, and METER_NUMBER will only appear once.
The setup:
CREATE TABLE TABLE_1 (
UNIT_ID NUMBER(12),
BFP_NUMBER NUMBER(12),
METER_NUMBER NUMBER(12));
Building insert statements:
SELECT
STATEMENT
FROM
(SELECT 'INSERT INTO TABLE_1 VALUES ('|| ABS(MOD(DBMS_RANDOM.RANDOM,20))||','|| ABS(MOD(DBMS_RANDOM.RANDOM,5))||','||ABS(MOD(DBMS_RANDOM.RANDOM,6))||');' STATEMENT FROM
INSERT INTO TABLE_1 VALUES (0,0,1); INSERT INTO TABLE_1 VALUES (0,3,0); INSERT INTO TABLE_1 VALUES (0,3,1); INSERT INTO TABLE_1 VALUES (0,3,1); INSERT INTO TABLE_1 VALUES (0,3,3); INSERT INTO TABLE_1 VALUES (0,4,2); INSERT INTO TABLE_1 VALUES (0,4,2); INSERT INTO TABLE_1 VALUES (1,0,0); INSERT INTO TABLE_1 VALUES (1,0,0); INSERT INTO TABLE_1 VALUES (1,1,4); INSERT INTO TABLE_1 VALUES (1,1,5); INSERT INTO TABLE_1 VALUES (1,2,5); INSERT INTO TABLE_1 VALUES (1,3,3); INSERT INTO TABLE_1 VALUES (10,0,3); INSERT INTO TABLE_1 VALUES (10,1,1); INSERT INTO TABLE_1 VALUES (10,1,1); INSERT INTO TABLE_1 VALUES (10,1,3); INSERT INTO TABLE_1 VALUES (10,2,3); INSERT INTO TABLE_1 VALUES (10,3,4); INSERT INTO TABLE_1 VALUES (10,4,2); INSERT INTO TABLE_1 VALUES (10,4,3); INSERT INTO TABLE_1 VALUES (11,0,1); INSERT INTO TABLE_1 VALUES (11,1,1); INSERT INTO TABLE_1 VALUES (11,1,4); INSERT INTO TABLE_1 VALUES (11,2,3); INSERT INTO TABLE_1 VALUES (11,3,3); INSERT INTO TABLE_1 VALUES (11,4,1); INSERT INTO TABLE_1 VALUES (11,4,2); INSERT INTO TABLE_1 VALUES (11,4,3); INSERT INTO TABLE_1 VALUES (11,4,3); INSERT INTO TABLE_1 VALUES (12,0,1); INSERT INTO TABLE_1 VALUES (12,1,1); INSERT INTO TABLE_1 VALUES (12,2,2); INSERT INTO TABLE_1 VALUES (12,2,2); INSERT INTO TABLE_1 VALUES (12,3,2); INSERT INTO TABLE_1 VALUES (13,0,0); INSERT INTO TABLE_1 VALUES (13,0,0); INSERT INTO TABLE_1 VALUES (13,0,5); INSERT INTO TABLE_1 VALUES (13,2,1); INSERT INTO TABLE_1 VALUES (13,2,1); INSERT INTO TABLE_1 VALUES (13,3,0); INSERT INTO TABLE_1 VALUES (13,4,2); INSERT INTO TABLE_1 VALUES (14,0,3); INSERT INTO TABLE_1 VALUES (14,0,5); INSERT INTO TABLE_1 VALUES (14,4,1); INSERT INTO TABLE_1 VALUES (15,0,1); INSERT INTO TABLE_1 VALUES (15,0,3); INSERT INTO TABLE_1 VALUES (15,1,0); INSERT INTO TABLE_1 VALUES (15,2,2); INSERT INTO TABLE_1 VALUES (15,4,4); INSERT INTO TABLE_1 VALUES (16,2,0); INSERT INTO TABLE_1 VALUES (16,2,3); INSERT INTO TABLE_1 VALUES (16,3,3); INSERT INTO TABLE_1 VALUES (16,3,5); INSERT INTO TABLE_1 VALUES (17,1,2); INSERT INTO TABLE_1 VALUES (17,3,5); INSERT INTO TABLE_1 VALUES (18,0,0); INSERT INTO TABLE_1 VALUES (18,2,1); INSERT INTO TABLE_1 VALUES (18,2,2); INSERT INTO TABLE_1 VALUES (18,3,1); INSERT INTO TABLE_1 VALUES (19,0,3); INSERT INTO TABLE_1 VALUES (19,1,3); INSERT INTO TABLE_1 VALUES (19,2,2); INSERT INTO TABLE_1 VALUES (19,3,1); INSERT INTO TABLE_1 VALUES (2,3,5); INSERT INTO TABLE_1 VALUES (3,0,5); INSERT INTO TABLE_1 VALUES (3,0,5); INSERT INTO TABLE_1 VALUES (3,3,4); INSERT INTO TABLE_1 VALUES (3,4,0); INSERT INTO TABLE_1 VALUES (4,0,1); INSERT INTO TABLE_1 VALUES (4,0,4); INSERT INTO TABLE_1 VALUES (4,1,1); INSERT INTO TABLE_1 VALUES (4,1,2); INSERT INTO TABLE_1 VALUES (4,2,1); INSERT INTO TABLE_1 VALUES (4,2,1); INSERT INTO TABLE_1 VALUES (4,3,0); INSERT INTO TABLE_1 VALUES (5,0,4); INSERT INTO TABLE_1 VALUES (5,1,4); INSERT INTO TABLE_1 VALUES (5,2,5); INSERT INTO TABLE_1 VALUES (5,3,1); INSERT INTO TABLE_1 VALUES (5,3,2); INSERT INTO TABLE_1 VALUES (5,4,0); INSERT INTO TABLE_1 VALUES (6,0,0); INSERT INTO TABLE_1 VALUES (6,1,0); INSERT INTO TABLE_1 VALUES (6,2,5); INSERT INTO TABLE_1 VALUES (6,2,5); INSERT INTO TABLE_1 VALUES (6,4,5); INSERT INTO TABLE_1 VALUES (7,0,3); INSERT INTO TABLE_1 VALUES (7,0,5); INSERT INTO TABLE_1 VALUES (7,2,1); INSERT INTO TABLE_1 VALUES (7,3,0); INSERT INTO TABLE_1 VALUES (7,3,5); INSERT INTO TABLE_1 VALUES (8,1,0); INSERT INTO TABLE_1 VALUES (8,3,2); INSERT INTO TABLE_1 VALUES (8,4,0); INSERT INTO TABLE_1 VALUES (8,4,5); INSERT INTO TABLE_1 VALUES (9,1,1); INSERT INTO TABLE_1 VALUES (9,2,0); INSERT INTO TABLE_1 VALUES (9,3,2); INSERT INTO TABLE_1 VALUES (9,4,2);
SELECT
*
FROM
TABLE_1
ORDER BY
UNIT_ID,
BFP_NUMBER,
METER_NUMBER;
UNIT_ID BFP_NUMBER METER_NUMBER
========== ========== ============
0 0 1 0 3 0 0 3 1 0 3 1 0 3 3 0 4 2 0 4 2 1 0 0 1 0 0 1 1 4 1 1 5 1 2 5 1 3 3 2 3 5 3 0 5
0 0 1 0 3 0 0 3 1 0 3 3 0 4 2 1 0 0 1 1 4 1 1 5 1 2 5 1 3 3 2 3 5 3 0 5
1 0 0 6 0 0 13 0 0 18 0 0 0 0 1 4 0 1 11 0 1 12 0 1 15 0 1 7 0 3 10 0 3 14 0 3 15 0 3 19 0 3 4 0 4 5 0 4 3 0 5 7 0 5 13 0 5 14 0 5 6 1 0
Can you provide a demonstration of what you are trying to accomplish?
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Nov 27 2006 - 19:05:18 CST
![]() |
![]() |