Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2006
    Posts
    7

    Unanswered: Select Max value from prior where conditions

    Originally posted this in the MSSQL forums. Sorry about that admins.

    I can't figure this out for the life of me. Wanted to know if it's possible to select certain date conditions in a query, then later reference those conditions and to only select the max of them.

    I need to do this dynamically as I do not know what the max value is. I've provided an example below:

    Select var1
    From table1
    where
    (
    (Date1 = '11/30/2005')
    OR
    (Date1 = '12/31/2005')
    )
    and Date1 = (Max of previously selected values e.g. '12/31/2005')

    What I can't figure out is how to dynamically retrieve the max of 11/31/2005 and 12/31/2005. Any ideas are greatly appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Try:
    Code:
    Select var1
    From table1
    where Date1 = (
    SELECT MAX(Date1) FROM TABLE1
    Where Date1 IN ('11/30/2005', '12/31/2005'));


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2006
    Posts
    7
    Not quite as easy as that. The first condition is generated through a software program; the second condition is what I have full control over. So the only code I have access to is the second condition following the 'and' statement.

    That's why I'm looking to reference the max of the two previously identified values. Thanks.

  4. #4
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Quote Originally Posted by wnm106
    Not quite as easy as that. The first condition is generated through a software program; the second condition is what I have full control over. So the only code I have access to is the second condition following the 'and' statement.

    That's why I'm looking to reference the max of the two previously identified values. Thanks.
    What part of the query can you not change? Can't you still use the basic idea of having a subquery to select the MAX?

  5. #5
    Join Date
    Apr 2006
    Posts
    7
    Everything except for the last condition is non-editable. I can use a Max sub query in the last condition, but that sub query will reference the entire table. If a record exists that is beyond the 12/31/2005 date then no results are returned.

    I've been trying to find a method to run a sub query against only those records that fall within the initial condition, but since sub query refers back to the entire table, and not the previously identified population, it's identifying records that I don't want to reference.

    My initial subquery looked like the following:
    And Date1 = (Select Max(Date) from Datetable dt where dt.Date = Max(Date1))

    The problem with this query is that it runs on a record by record basis, so if one record has a maximum value of '11/30/2005' it would be returned. If another record had a maximum value of '12/31/2005', it would also be returned.

    What I was looking to accomplish is instead of referring back to the table, refer to the previously defined population, and select max value from the first condition (12/31/2005). This way the sub query wouldn't have to identify the max value for each record, but instead identify the max value of dates that were already defined. Hope that clarifies a little.

  6. #6
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Everything except for the last condition is non-editable.
    So you are just passing in a condition? I thought you were writing the query and one of the conditions was static? Still not really sure which part of the query you are writing and which part is static. Could you rewrite the query and explicitly explain which parts you can/cannot change (why the subquery is difficult)?

    Depending on how limited you are, you could also try using the query you already have as the subquery .

  7. #7
    Join Date
    Apr 2006
    Posts
    7
    I've highlighted the undeditable portion of the query below in red. I also provided an example table. The way the initial date conditions work is that the user selects a population from the software frontend. The query is generated through the software.

    I then have the ability to restrict the results that are returned through the final condition (in black). Based on the example table, I'd expect the query to return two results (both of the 12/31/2005 rows). Instead it will return 3 results (Var1 = 1,3,4).

    The difficult part is that the user selected dates are variable, so if they only selected '11/30/2005' I would want to return each of the 11/30/2005 rows. Right now it would only return those results for Var1 = 1,4. Hope this helps to clarify. Thanks again for all your help.

    Select count(*)
    From DateTable
    where
    (
    (Date1 = '11/30/2005')
    OR
    (Date1 = '12/31/2005')
    )
    AND

    Date1 = (Select Max(dt.Date1) from DateTable dt where dt.Date1 = (DateTable.Date1))

    DateTable
    Var1 Date1
    1___ 10/31/2005
    1___ 11/30/2005
    2___ 11/30/2005
    2___ 12/31/2005
    2___ 1/1/2006
    3___ 12/31/2005
    4___ 11/30/2005

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Or maybe something like
    Code:
    Select var1
    From table1
    where
    (
    (Date1 = '11/30/2005')
    OR
    (Date1 = '12/31/2005')
    )
    ORDER BY Date1 DESC
    FETCH FIRST ROW ONLY
    (This is DB2-specific syntax; other platforms may have other ways to do equivalent things.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Apr 2006
    Posts
    7
    The only location I would have the ability to do that is within the sub-query itself. The rest of the query is generated by the application. If I use the (Fetch first row, Top 1, ...) type commands, it works, but only for the particular Var# that it's running for.

    So when it ran on Var1 it would return the 11/30/2005 record, which then would return a count value for the Var1. Since I only what to return those records with a 12/31/2005 record, I'll retrun incorrect results. Thanks for the suggestion though.
    Last edited by wnm106; 04-10-06 at 10:44.

  10. #10
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    So the red part of the query is generated by your program and you have no way of accessing the two dates separately? It is using COUNT() as well?

    If you GROUP BY Date1 and then ORDER, that should solve your problem, right?

  11. #11
    Join Date
    Apr 2006
    Posts
    7
    You are correct. The red part is controlled by the application. This also includes any Group by or Order by statements that apply to the overall query.

    The only location I could use a Group by or Order by in within my sub query. Since the sub query runs on each variable, the condition would return true where Var1 = 1. This would match on the 11/30/2005 records. It would fail on the Var1 = 2 since that would return 1/1/2006, but I would like it to pass for that instance.

    I need some way to reference the Date1 values without actually knowing what the user selected. This has been a real thorn in my side. Thanks again.

Posting Permissions

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