Re: Using distinct across several columns

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 15 Jan 2003 11:20:57 +0200
Message-ID: <b03253$qnn$1@ctb-nnrp2.saix.net>

Steve Wicklund wrote:

> Hello all; I have fiddled with this syntax for a bit now. what is the
> syntax for finding results that are unique across several rows-- but
> not all. E.G.: a table has columns name, phone, street and
> order_number. I want to have all four columns retunred-- but one row
> for every unique combination of name, street and phone.
>
> name street phone order_number
>
> 1) bob Oak st 555-1212 2525
> 2) bob Oak st 555-1212 2521
> 3) fred Oak st 555-1212 2529
> 4) jerry Pine st 555-1212 2522
> 5) bob Oak st 555-1212 2527
> 6) jerry Pine st 402-1212 2560

To answer your basic question - you can either use DISTINCT or GROUP BY.

However, neither on their own will solve your problem.

The problem in your case is that GROUP BY will eliminate rows. Consider: SELECT
  name, street, phone
FROM foor
GROUP BY name, street, phone

This will give you a unique list as you want. Minus the order number.

Adding the order number as part of the grouping, makes it part of the unique identification of the results - which is not what you want.

Adding the order number as an aggregation (e.g. MAX(order_number) ), will work, but you will now exclude order numbers.

The solution is to join the GROUP BY results back to the original table. You use the unique columns from the GROUP BY data set and the invoice number from the table.

I'm selecting to use GROUP BY (but DISTINCT will also do the job in the SQL below).

Something like this:
SELECT

  g.name,
  g.street,
  g.phone,
  t.order_number
FROM foo t,        /* the table */
     ( SELECT
         name, street, phone
       FROM foo
       GROUP BY name, street, phone
     ) g          /* the group by data set */

WHERE t.name = g.name

The assumption with the above join being that the name is the unique identifier and can be used in this way.

Hopes this gives you the basic idea and concept of what to do.

Oh yeah - just be wary of the explain plan for the above SQL (especially if the above format results in a nested loop join to the group by data set - that will really hurt performance).

The GROUP BY data set will have to be treated with a full table scan. If this data set is large, you may more than likely have performance issues. I would suggest that you put these types of inline SQLs through explain plans, change the join order and method, to determine the best way for it to be processed.

--
Billy

Received on Wed Jan 15 2003 - 03:20:57 CST