Unique command in SQL

Smiley · #1 (**permalink**) 07-02-02, 00:32

In a SQl query, when is 'Unique' command used and when it is not used in a SELECT statement?

r937 · #2 (**permalink**) 07-02-02, 15:42

perhaps you are thinking of SELECT DISTINCT

UNIQUE is a constraint attribute used when you CREATE a table or index

rudy
http://rudy.ca/

Smiley · #3 (**permalink**) 07-02-02, 16:14

Yes you are right, I was talking about SELECT DISTINCT.
Does it make any difference if I use Distinct in every Select Statement?

r937 · #4 (**permalink**) 07-02-02, 16:19

does it make a difference? yes and no

yes, it makes a difference when there are duplicate rows in the result of the SELECT, because then DISTINCT will ensure that there aren't any duplicates

no, it makes no difference when there aren't any duplicates, because then DISTINCT won't be able to remove any

note that DISTINCT takes a lot of extra processing, so don't just throw it into the SELECT if you don't have to!

rudy

rnealejr · #5 (**permalink**) 07-02-02, 17:05

FYI

Some databases, like Oracle and Informix, suport both select distinct (ansi standard) and select unique (extension of the ansi standard for backward compatibility).

pep11 · #6 (**permalink**) 09-13-11, 04:23

select unique a, b from c

will this give unique results on a only or a and b together as a unique entity?

shammat · #7 (**permalink**) 09-13-11, 04:31

Quote:

Originally Posted by pep11

select unique a, b from c

will this give unique results on a only or a and b together as a unique entity?

a,b together

JarlH · #8 (**permalink**) 09-13-11, 05:27

Just a note, don't use UNIQUE even if your current dbms supports it (at the moment.) Use the ANSI standard's DISTINCT and you wont get problems when you change dbms in the future.

It's good practice to chose the standard syntax if your product supports both standard and vendor specific versions.

shammat · #9 (**permalink**) 09-13-11, 05:53

Quote:

Originally Posted by JarlH

It's good practice to chose the standard syntax if your product supports both standard and vendor specific versions.

I second that.
Very good advice!

pep11 · #10 (**permalink**) 09-14-11, 07:44

Quote:

Originally Posted by shammat

a,b together

okk.. so, if i want to get unique values for a irrespective of but still want to select b how do i write it?

JarlH · #11 (**permalink**) 09-14-11, 08:21

If your table contains

A B
= =
1 a
1 b
2 a
2 c
3 b
3 b
4 d

What result do you want?

r937 · #12 (**permalink**) 09-14-11, 10:06

Quote:

Originally Posted by pep11

okk.. so, if i want to get unique values for a irrespective of but still want to select b how do i write it?

easy...

Code:

SELECT a
     , MIN(b) AS some_b
  FROM daTable
GROUP
    BY a

Internet Services

Web Development

Digital Marketing

Consumer Tech