Re: sql question - distinct

Steve Howard wrote:
> soalvajavab1_at_yahoo.com wrote:
> >
> > I want DISTINCT only applies to UNIT_ID but I also have data for
> > BFP_NUMBER, METER_NUMBER in each result rows.
> >
>
> I'm still not clear on what you want. Using Galen's example above, do
> you want to see...
>
> UNIT_ID BFP_NUMBER METER_NUMBER
> 1 10 100
> 11 101
> 2 12 102
>
> ?
>
> Regards,
>
> Steve

It seems like the above would be too easy of a solution to the problem, which would require something like this using the sample table and data that I posted:
SELECT
  DECODE(SIGN(ROW_NUMBER() OVER (PARTITION BY UNIT_ID ORDER BY BFP_NUMBER,METER_NUMBER)-1),0,UNIT_ID,NULL) U_ID,   BFP_NUMBER,
  METER_NUMBER
FROM
  TABLE_1
ORDER BY
  UNIT_ID,
  BFP_NUMBER,
  METER_NUMBER;       U_ID BFP_NUMBER METER_NUMBER
========== ========== ============

	 0	    0		 1
		    3		 0
		    3		 1
		    3		 1
		    3		 3
		    4		 2
		    4		 2
	 1	    0		 0
		    0		 0
		    1		 4
		    1		 5
		    2		 5
		    3		 3
	 2	    3		 5
	 3	    0		 5
		    0		 5
		    3		 4
		    4		 0
	 4	    0		 1
		    0		 4
		    1		 1
		    1		 2
		    2		 1
		    2		 1
		    3		 0
	 5	    0		 4
		    1		 4
		    2		 5
		    3		 1
		    3		 2
		    4		 0
	 6	    0		 0
		    1		 0
		    2		 5
		    2		 5
		    4		 5
	 7	    0		 3
		    0		 5
		    2		 1
		    3		 0
		    3		 5
	 8	    1		 0
		    3		 2
		    4		 0
		    4		 5
	 9	    1		 1
		    2		 0
		    3		 2
		    4		 2
	10	    0		 3
		    1		 1
		    1		 1
		    1		 3
		    2		 3
		    3		 4
		    4		 2
		    4		 3
	11	    0		 1
		    1		 1
		    1		 4
		    2		 3
		    3		 3
		    4		 1
		    4		 2
		    4		 3
		    4		 3
	12	    0		 1
		    1		 1
		    2		 2
		    2		 2
		    3		 2
	13	    0		 0
		    0		 0
		    0		 5
		    2		 1
		    2		 1
		    3		 0
		    4		 2
	14	    0		 3
		    0		 5
		    4		 1
	15	    0		 1
		    0		 3
		    1		 0
		    2		 2
		    4		 4
	16	    2		 0
		    2		 3
		    3		 3
		    3		 5
	17	    1		 2
		    3		 5
	18	    0		 0
		    2		 1
		    2		 2
		    3		 1
	19	    0		 3
		    1		 3
		    2		 2
		    3		 1

The SQL code that OP provided in his other post: SELECT MAX(BFP_NUMBER), MAX(METER_NUMBER), UNIT_ID FROM TABLE_1
GROUP BY UNIT_ID; Produces the following results:
MAX(BFP_NUMBER) MAX(METER_NUMBER) UNIT_ID

=============== ================= ==========
	      3 		5	   1
	      4 		4	  11
	      4 		5	  13
	      4 		5	   6
	      4 		5	  14
	      3 		5	   2
	      3 		4	   4
	      4 		5	   5
	      3 		5	  17
	      4 		5	   8
	      3 		2	  18
	      4 		5	   3
	      3 		5	   7
	      4 		3	   0
	      4 		4	  10
	      3 		2	  12
	      4 		4	  15
	      3 		5	  16
	      3 		3	  19
	      4 		2	   9

Note that the first row that appears in the OP's result set does not exist in the data, there is no combination of UNIT_ID=1, BFP_NUMBER=3 and METER_NUMBER=5 in the original data.

soalvajavab1, please provide actual output of what the report should look like. Provide the input data and the expected output, and someone may be able to help.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Nov 28 2006 - 18:41:23 CST