SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: Is unique?

  1. #1
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is unique?

    hi,

    I'm trying to check if a column has unique values in a field, how to do that?
    (I tried IS UNIQUE condition but it doesn't work)

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Try the following:

    Code:
    SELECT a.column
    FROM table_name as a
      LEFT JOIN table_name as b
        ON b.column = a.column
    HAVING COUNT(b.column) = 1
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT 
        COUNT(col) = COUNT(DISTINCT col) 
                AS col_is_unique
    FROM table

  4. #4
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by stereofrog
    Code:
    SELECT 
        COUNT(col) = COUNT(DISTINCT col) 
                AS col_is_unique
    FROM table
    That's a very smart solution. However, it won't work unless you add a GROUP BY clause.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  5. #5
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Damn, none of these does work, I must have something wrong somewhere

  6. #6
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I tested sterefrog's solution (after I added a GROUP BY clause to it), and it worked like a charm. Are you sure that all field/table names are correct? This is the query I used:

    Code:
    SELECT str, COUNT(str) =
      COUNT(DISTINCT str) as is_unique
    FROM test_table GROUP BY str
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  7. #7
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes it worked when I added the GROUP BY statement ;o)
    Thanks to you guys

  8. #8
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lilleman
    However, it won't work unless you add a GROUP BY clause.
    Why?

  9. #9
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by stereofrog
    Why?
    Since no GROUP BY clause exists, your query will check if the total number of rows in the table is the same as the number of unique values in the column. So basically, your query check if all values are unique, not if a specific value is unique.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  10. #10
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, and it's what OP has asked for.

    Quote Originally Posted by HenriIV
    I'm trying to check if a column has unique values in a field, how to do that?
    If you need to count some specific value, just use

    SELECT COUNT(*) FROM .... WHERE col=....

    If result isn't 1, the value is not 'unique'.

    Quote Originally Posted by lilleman
    SELECT str, COUNT(str) =
    COUNT(DISTINCT str) as is_unique
    FROM test_table GROUP BY str
    COUNT(DISTINCT x) together with "GROUP BY x" always gives 1, so it's no need to calculate it. This returns all values that occur more than once:

    Code:
    SELECT col, COUNT(*) AS c, 
       FROM tbl
       GROUP by col
       HAVING c > 1

  11. #11
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by stereofrog
    Yes, and it's what OP has asked for.
    I was under the impression that he wanted to check whether or not a specific value is unique (which is what your query does if a GROUP BY clause is specified).
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  12. #12
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, the main thing is that it works for him now, whatever he wanted

  13. #13
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the main thing is that it works for him now
    Yes, it' ok now ;o)

    The fact is that it always return 1 without GROUP BY, with GROUP BY it works perfectly


Bookmarks

Posting Permissions

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