Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Location
    Orange County, CA
    Posts
    5

    Unanswered: Returning multiple MIN values of a field based upon different criteria

    The idea is to find the Min `Inventory` value for each `Tank` for a specified `Site Number` not the single lowest inventory value for a specific `Site Number`

    Code:
    SELECT DISINCT `Tank`
    FROM `detail`
    WHERE `Site Number` = 9201906000;
    Returns:

    |Tank
    |-----
    |1
    |2
    |3
    |-----

    Code:
    SELECT `Tank`, `Grade`, Min(`Inventory`) as 'Min Inventory'
    FROM `detail`
    WHERE `Site Number`= 9201906000 and `Tank` = *Variable Criteria*;
    Desired Result: is attached as image

    The Field named `Tank` contains a simple 1, 2, 3, or 4 value that identifies the tank.

    All fields are located in the same table. I can do this w/ VBScript, but I'm sure there has to be a way using only SQL.
    Attached Thumbnails Attached Thumbnails result.GIF  

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Chad,

    try using GROUP BY. This should resolve your problem.

    SELECT `Tank`, `Grade`, Min(`Inventory`) as 'Min Inventory'
    FROM `detail`
    WHERE `Site Number`= 9201906000 and `Tank` = *Variable Criteria*
    GROUP BY `Tank`, `Grade`;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2010
    Location
    Orange County, CA
    Posts
    5

    Simple Enough

    Well Jebus Christmas, my brain was farting. Thank you for wafting away the fumes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •