SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot newspire's Avatar
    Join Date
    Mar 2005
    Location
    Houston, TX
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Least Cost Provider

    I've got a problem that I thought would be simple but the solution is not coming to me.

    I have one table:
    id
    location
    provider
    rate

    The rows look like

    1 TX CompanyA 2.25
    2 TX CompanyB 2.00
    3 NY CompanyB 3.00
    4 TN CompanyA 1.25
    5 TN CompanyB 1.50

    I need to get the cheapest rate and provider for each location.

    Results:
    TX CompanyB 2.00
    NY CompanyB 3.00
    TN CompanyA 1.25

    Thanks for the help!
    NewSpire

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select location
         , provider
         , rate
      from datable d
     where rate = (select min(rate)
                     from datable
                    where location = d.location)
    if you have 2 providers with the same cheapest rate in an area, this will return both.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    this might execute faster:
    Code:
    select location
         , provider
         , rate
      from datable d
     where (location, rate) in (select location
                                     , min(rate)
                                  from datable
                                group
                                    by location)
    or this:
    Code:
    select location
         , provider
         , rate
      from datable d
      join (select location
                 , min(rate) as min_rate
              from datable
            group
                by location) dt
        on dt.location = d.location
       and dt.min_rate = d.rate
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck View Post
    or this:
    i u
    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
  •