Results 1 to 12 of 12
  1. #1
    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps you are thinking of SELECT DISTINCT

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

    rudy
    http://rudy.ca/

  3. #3
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    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

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

  6. #6
    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?

  7. #7
    Join Date
    Nov 2003
    Posts
    2,821
    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

  8. #8
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    87
    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.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,821
    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!

  10. #10
    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?

  11. #11
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    87
    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?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •