Re: sql question - distinct

Thanks Guys for replies but still I did not get what I am trying to accompolish. Here it is:

I do not want to makes certain that any combination of UNIT_ID, BFP_NUMBER, and
METER_NUMBER will only appear once.

I want to makes certain that UNIT_ID only appear once but I get the values for BFP_NUMBER, and METER_NUMBER also in each result row

But as I said and you mentioned too when I run SELECT DISTINCT UNIT_ID, BFP_NUMBER, METER_NUMBER it makes certain that any combination of UNIT_ID, BFP_NUMBER, and METER_NUMBER will only appear once.

I want DISTINCT only applies to UNIT_ID but I also have data for BFP_NUMBER, METER_NUMBER in each result rows.

Please advise

Charles Hooper wrote:
> 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 Tue Nov 28 2006 - 11:51:00 CST