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

Home -> Community -> Usenet -> c.d.o.misc -> Re: How can we make distinct of one particular column!

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

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: 1998/01/06
Message-ID: <34b192a7.2073691@newshost.us.oracle.com>#1/1

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).

On Mon, 05 Jan 1998 11:29:24 +0100, Pedro Nuno Gomes Pimenta <p.pimenta_at_ifb.pt> 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
>

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

Original text of this message

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