SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: distinct values

  1. #1
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    distinct values

    In my select statement, Im grabbing zip codes, city names, how the distance that city is from my house.

    Centreville 20121 0.000000
    Mount Vernon 22121 1.490575
    Centreville 20120 1.848611
    Manassas 20112 3.378830
    Manassas 20113 3.378830
    Manassas 20111 3.387286
    ...

    I only want the city to appear once, and the value I want is the City/Zip/Distance combination that claims to be the closest (smallest distance). What do I need to add to my sql statement?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    select city,zip,distance
    from yourtable xx
    where distance =
    ( select min(distance)
    from yourtable
    where city = xx.city )

    rudy
    http://rudy.ca/

  3. #3
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can I do that in MySQL?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, mysql does not support subselects, they recommend you do it in two steps

    see 3.5.2 The Row Holding the Maximum of a Certain Column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •