If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > Unique command in SQL

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2002
Posts: 11
Unique command in SQL

In a SQl query, when is 'Unique' command used and when it is not used in a SELECT statement?
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
perhaps you are thinking of SELECT DISTINCT

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

rudy
http://rudy.ca/
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2002
Posts: 11
Yes you are right, I was talking about SELECT DISTINCT.
Does it make any difference if I use Distinct in every Select Statement?
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
DISTINCT

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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2002
Posts: 2,232
FYI

Some databases, like Oracle and Informix, suport both select distinct (ansi standard) and select unique (extension of the ansi standard for backward compatibility).
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2011
Posts: 3
select unique a, b from c

will this give unique results on a only or a and b together as a unique entity?
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,779
Quote:
Originally Posted by pep11 View Post
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Dec 2008
Location: At work...
Posts: 84
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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,779
Quote:
Originally Posted by JarlH View Post
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!
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Sep 2011
Posts: 3
Quote:
Originally Posted by shammat View Post
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?
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Dec 2008
Location: At work...
Posts: 84
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?
Reply With Quote
  #12 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
Quote:
Originally Posted by pep11 View Post
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On