Distinct and Unique

Hi
Distinct(in oracle and SQL server) and Unique(in oracle) are they both same
Can we use them interchangeably?
Thanks
JP> Distinct(in oracle and SQL server) and Unique(in oracle) are they both
> same
> Can we use them interchangeably?
I can't speak for UNIQUE in Oracle, so I don't know if it behaves the same
as DISTINCT. But personally, I find very few circumstances where I need to
use DISTINCT...|||>> Distinct(in oracle and SQL server) and Unique(in oracle) are they both sa
me Can we use them interchangeably? <<
No. That is one of MANY Oracle dialect screw-ups to prevent Standard,
portable code. The UNIQUE() constraint is part of the DDL; the
DISTINCT option is part of the DML.|||JP wrote:
> Hi
> Distinct(in oracle and SQL server) and Unique(in oracle) are they both sam
e
> Can we use them interchangeably?
> Thanks
> JP
Instead of Oracle specific
select unique n from(
select 1 n from dual
union all
select 1 n from dual) t
use
select DISTINCT n from(
select 1 n from dual
union all
select 1 n from dual) t
that will give you exactly the same functionality AND it will be
portable|||or for unions use union instead of union all
select 1 n from dual
union -- all take all ouy of you need distinct in a union
select 1 n from dual
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Alexander Kuznetsov wrote:
> JP wrote:
> Instead of Oracle specific
> select unique n from(
> select 1 n from dual
> union all
> select 1 n from dual) t
> use
> select DISTINCT n from(
> select 1 n from dual
> union all
> select 1 n from dual) t
> that will give you exactly the same functionality AND it will be
> portable|||I deliberately wrote union all to provide duplicates|||Okay, my bad ;-(
I just wanted to point out that with unions you can use union instead
of union all to get a distinct resultset
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Alexander Kuznetsov wrote:
> I deliberately wrote union all to provide duplicates