Cross-tab query definition problem

Hi there,
I have got this problem. I want to know which concrete combination of 7
variables gives the maximum value of some non-linear function. I have
created 7 tables (per 1 column) with all possible values of each
variable (e.g. for the first variable: 0.00,0.01,0.02,0.03,...,1.00)
and then made the cross-tab query based on these 7 tables: Select
Max(...) from dbo.var1 cross join dbo.var2 ... cross join dbo.var7.
This query finds the maximum value of the function but how should I
find out which concrete combination of the variables is connected with
this max.value?
I know that it is not clever to use relational DBS for this problem but
I want to find out how quickly SQL server calculates the result.
Thanks a lot.
MilanHello, Milan
You are talking about cross joins, not crosstabs.
You can get the values using something like this:
SELECT Col1, Col2, ...
FROM Table1, Table2, ...
WHERE YourExpression=(
SELECT MAX(YourExpression)
FROM Table1, Table2, ...
)
If you have 7 tables with 100 values each, that would be a total of
100000000000000 combinations (that is one hundred thousand billions). I
would not dare to think how many years it would take for SQL Server to
return the result of this query.
Razvan|||Hello Razvan,
Thanks for your answer. I am sorry for that mistake, of course I ment
cross joins :).
The problem with your solution is that expression for every
combinations have to be calculated two-times. Since the total number of
combinations is great (the number of rows in each table is: 100, 20,
10, 10, 10, 5, 20 resp., which gives 200 mil. combinations) there is a
serious problem with calculation. Is it somehow possible to obtain the
concrete combination of variables which yield the maximum value of that
function within one single query'
Thank you.
Milan|||Another way would be:
SELECT TOP 1 WITH TIES Col1, Col2, ...
FROM Table1, Table2, ...
ORDER BY YourExpression DESC
Razvan