Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sql question - distinct

Re: sql question - distinct

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 27 Nov 2006 17:05:18 -0800
Message-ID: <1164675918.532528.321350@f16g2000cwb.googlegroups.com>


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
    DBA_OBJECTS WHERE ROWNUM<=100)
ORDER BY
  STATEMENT;
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

...
100 ROWS SELECTED SELECT DISTINCT
  *
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		 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

...
89 ROWS SELECTED SELECT DISTINCT
  *
FROM
  TABLE_1
ORDER BY
  BFP_NUMBER,
  METER_NUMBER,
  UNIT_ID;    UNIT_ID BFP_NUMBER METER_NUMBER
========== ========== ============
	 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US