Post reply

SELECT DISTINCT question

  • August 18, 2006 at 3:27 pm

    #113271

    Hi,

    Would someone please help me understand the difference between these two statements

    1) SELECT DISTINCT id FROM my_table;

    2) SELECT DISTINCT id, name FROM my_table;

    For my test data statement #1 returned 29 records where as statement #2 returned 30 records which two of them are duplicated id.

    Thanks,

    Tuan

  • August 18, 2006 at 3:43 pm

    #655748

    SELECT DISTINCT will produce 1 record for every occurrence of the items in the SELECT list.

    In the first example if you have two shared ID values then only one will be returned.

    In the 2nd example if you have two shared ID values but the names are different then both will be reported. If both the ID and name are shared then only one will be reported.

    LinkedIn Profile

  • August 18, 2006 at 3:45 pm

    #655750

    DISTINCT works across all columns in the resultset, not just the column immediately after the keyword DISTINCT.

    SELECT DISTINCT id, name gives you all the unique id AND name combinations. If you have the same id with different names, then the resultsets of your 2 queries are guaranteed to be different.

  • August 18, 2006 at 3:56 pm

    #655752

    Very helpful. Thanks very much to both David and PW!

    Have a great weekend!

    Tuan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply