Re: How can we make distinct of one particular column!

From: Pedro Nuno Gomes Pimenta <p.pimenta_at_ifb.pt>
Date: 1998/01/06
Message-ID: <34B1F802.A6494174@ifb.pt>#1/1

Pedro Nuno Gomes Pimenta wrote:

> Happy new year everybody!
>
> If i have a select statement like this:
>
> SELECT column1, column2,column3 FROM table......
>
> How can i retrieve the query eliminating the repetitions of column2.
> The distinct function eliminates the repetitions of all columns.
>
> Thanks!
> Pedro

  Well that's one solution by Joel R. Kallman:

What you are saying is that you want to select only one row from this table for each unique value of "column2". I am assuming that you do not care which row gets selected.

By issuing the query:

        SELECT MIN(rowid) FROM table GROUP BY column2

you are identifying only one row from table for each unique value of column2.

Now, by turning this into a subselect, you ultimately get the query that you want:

        SELECT column1, column2, column3 FROM table
        WHERE rowid IN
                (SELECT MIN(rowid) FROM table GROUP BY column2)

This will work for everything but index-organized tables in Oracle8 (which do not have rowid's).

Thanks!

Joel

Joel R. Kallman                          Enabling the Information Age!
Oracle Government, Education, & Health
Columbus, OH                             http://govt.us.oracle.com
jkallman@us.oracle.com                   http://www.oracle.com

---

The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Tue Jan 06 1998 - 00:00:00 CST