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.

Try:

Code:

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

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?

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.

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 .

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

Or maybe something like(This is DB2-specific syntax; other platforms may have other ways to do equivalent things.)

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.

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?

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.