Re: DISTINCT values

In article <gHWK7.22$Mi4.739_at_nsw.nnrp.telstra.net>, sweidanz_at_yahoo.com says...
>
>Folks,
>I am a bit confused with whats the difference between:
>SELECT DISTINCT column1, column2
>and
>SELECT DISTINCT (column1) , column2
>

distinct is a modifier to the SELECT keyword. By default, we:

select ALL column1, column2 ....

you can

select DISTINCT column1, column2

Now, wrapping () about column1 does not do anything because distinct is not a FUNCTION -- its a modifier to the SELECTION that says "distinct this result set after you get it".

I dont see how:

>As far as i used to understand, is the first one will give you the
>distinct rows. and the second one will give the distinct column1 even if
>there are non unique values in column2.
>

could work. If you want the second query to return data like you have, you could:

select column1, MAX(column2) from t group by column1

that'll "distinct" column1 -- but you have to tell us what value from column2 you want to keep -- choices like min, max, avg, sum, count, etc are available.

>For example,
>column1= acct_no
>column2= transaction_date
>and the data as follows:
>acct_no transaction_date
>1 01/12/2000
>1 02/12/2000
>1 02/12/2000
>2 03/02/2001
>2 04/02/2001
>
>so if i run the first query i should get:
>
>1 01/12/2000
>1 02/12/2000
>2 03/02/2001
>2 04/02/2001
>
>and as i used to understand, if i run the second query:
>1 01/12/2000
>2 03/02/2001
>
>BUT, its not returning what i expected. the second query returned exactly
>the same as first query.
>
>** Anyone can clarify if there is any difference
>** and if there is an efficient way of getting the second result.
>
>thanks,
>ZS
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp