SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Thread: Distinct

  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    London
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Distinct

    Hi,

    I'm pretty new to SQL but need to do the following in a query:

    Select Disctinct(address)
    FROM myTable

    The problem is the "address" is made up of five table columns address1, address2, city, and zipcode. How would I incorporate this into the query?
    Thanks,


    Richard

  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,

    Take a look at the CONCAT function.

    Yours, Erik.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Distinct is always applied to all columns in the result (so using concat will not make any difference).

    Code:
    select distinct adress1,adress2, city, zipcode from t
    Why do write

    Code:
    distinct (adress)
    ?

    distinct is not a function.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    London
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your right I actually mean't:

    select distinct address
    from myTable

    But I'm still no better off

    Any suggestions of how to select an address once when the address is split into five columns?

  5. #5
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So you want the address returned into one variable?

  6. #6
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Could you give some samples of your data and the expected result?

  7. #7
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    London
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't strictly want the address returned in one variable I just want to select only one exact address match i.e.

    address1, address2, city, zipcode, country
    1 high street, , london, abc 123, UK (selected)
    2 high street, greenwich, london, abc 123, UK (selected)
    1 high street, greenwich, london, abc 123, UK (selected)
    1 high street, , london, abc 123, UK (not selected as matched result one)

    Does this make sense?

  8. #8
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    In that case the query i posted

    Code:
    select distinct adress1,adress2, city, zipcode from t
    should be sufficient.

  9. #9
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    London
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK thanks, I'll give it a whirl

  10. #10
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    London
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that did work. However, I do have one last question that I can't quite figure out...

    I also needed the ID number (passesID) for each of the records. Unfortunately when I included the passesID in the select statement it is using it within the distinct feature makeing each of the entries automatically unique.
    i.e.
    SELECT DISTINCT address1, address2, city, zipcode, country, passesID
    FROM myTable

    How do I get round this last bit?
    Thanks,

    Richard

  11. #11
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Which value of passesID within each distinct group do you want? Highest lowest?

    Code:
    select max(passesId),adress1,adress2, city, zipcode from t
    group by adress1,adress2, city, zipcode

  12. #12
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    London
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks thats worked a treat.


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
  •