1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

distinct on multiple columns

Discussion in 'SQL PL/SQL' started by krithika@2001, Sep 14, 2009.

  1. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Hi


    I have a problem with distinct

    my query is "select distinct code and ID from tbl1"

    Code id
    1 2
    1 3

    In this I will get count as 2.But I want ID's for distinct codes
    that is my record count should be only one.

    Can anyone plsss help.


    Thanks and Regards
    Krithika
     
  2. Shekhar81

    Shekhar81 Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    ID's for distinct codes? in the data you gave your distinct code is '1'. The ids for that are 2 and 3, so you will obviously get two rows. What is the problem?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I think you have a problem understanding DISTINCT; this is a set operator, not a column-specific operator, so it's working correctly as the sets (1,2) and (1.3) are distinct. If we write a query to return what you think should be returned you will be surprised that both 2 and 3 are displayed:

    Code (SQL):
    SQL> SELECT DISTINCT code, id
      2  FROM tbl1
      3  ORDER BY 1,2;

          CODE         ID
    ---------- ----------
             1          2
             1          3
             2          1
             3          2
             4          2

    SQL>
    SQL> SELECT DISTINCT code
      2  FROM tbl1
      3  ORDER BY 1;

          CODE
    ----------
             1
             2
             3
             4

    SQL>
    SQL> SELECT id
      2  FROM tbl1
      3  WHERE code IN (SELECT DISTINCT code FROM tbl1)
      4  ORDER BY code, id;

            ID
    ----------
             2
             3
             1
             2
             2

    SQL>
    I don't understand what it is you're wanting your query to return. WHICH id value do you expect for code 1? If we knew that someone could point you to the correct query to get those results. As it is now we have no idea what you want to see, so getting the 'right' query is next to impossible.