SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    Probably eating pie mitsubishi's Avatar
    Join Date
    Sep 2001
    Location
    England, UK
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    "DISTINCT", but distinct what?

    Hi, I've been looking all over but can't find a clear answer.

    Code:
    SELECT DISTINCT a,b,c ...etc...
    What is distinct? 'a' or the entire rowset?

    Also is this comptible on mSQL, access, oracle and postgres as well as mySQL?

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DISTINCT row. If you want a distinct set of columns you can try 'group by' but it works only in certain circumstances.

  3. #3
    Probably eating pie mitsubishi's Avatar
    Join Date
    Sep 2001
    Location
    England, UK
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, the whole row is distinct, not just individual columns.
    Code:
    +---------+---------+---------+
    |    a    |    b    |    c    |
    +---------+---------+---------+
    |    1    |    abc  |   34    |
    |    1    |    abc  |   21    | < this will be returned
    |    1    |    abc  |   21    | < but this wont?
    |    2    |    abc  |   34    |
    +---------+---------+---------+
    And
    Code:
    SELECT DISTINCT a,b,c FROM table ORDER BY a,b
    Is not going to fetch me the 2nd and 4th rows?

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Re: first question. Yes, it will strip out duplicate rows.

    Re: Second query -- no, you're ORDERing BY, not GROUPing by. If you were to group by the three columns it would/should imitate the distinct. Of course, it probably won't work without an aggregate function tho.

  5. #5
    Probably eating pie mitsubishi's Avatar
    Join Date
    Sep 2001
    Location
    England, UK
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Got it!

    Right, cheers Matt, the mySQL manual isn't terribly helpful with a lot of things, I suppose it assumes you know SQL already.

    Better go and read up on 'GROUP BY' it's the last major thing I havn't learnt yet.

    Now I just need to find some info on cross-compatible syntax.


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
  •