[quoted text, click to view]

Hi Bob,

I simplified the problem for posting. Heres more like what it really
is, so you can see the problem with taking the min() or max() of the
argument of the function:

SELECT
id as '#',
name,
dbo.calculate(table_name.field1 + table_name.field2) as
'Calculation Result'
FROM
table_name

now if you try max(table_name.field1 + table_name.field2) it won't work
because you are no longer naming a column to take the max of, correct?

So I need something to get the min and max values of the *result set*
from the above select statement. Is that possible?

Thanks for all of your help!
-Mike

[quoted text, click to view]

Did you try it and get incorrect results?

[quoted text, click to view]

Huh? I have no idea about what that function does, but it seems to be
taking a value (the id of the current row), doing something with that
value, and returning a single value. Getting the min or max of a single
value seems to be pretty meaningless, doesn't it?

[quoted text, click to view]

Again ... I have no clue what calculate() is doing ...

It will probably help if you provide DDL, sample data and the desired
results from that sample data:
http://www.aspfaq.com/5006


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[quoted text, click to view]

No I haven't, because of this line:

dbo.calculate(min(table_name.id)) as 'Calculation Result'

it seems to me that it is definitely possible to get the correct
results with this line. However, it is not guaranteed in certain
cases. For example, lets say the id ranges from 1 to 12. If I
understand things correctly, then, you will get as 'Calculation Result'
the following:
dbo.calculate(2) as the min
dbo.calculate(12) as the max

but, what if the calculate function multiplies its argument by -1?
Then you would have
-2 as the min
-12 as the max

of course I have not been doing SQL for long so I could be totally
wrong in my understanding of how functions work (please say so if this
is the case).

moments ago I just got it working by doing the following:

creating a temp table
inserting the results from the stored procedure into the temp table
selecting min() from the temp table
union
selecting max() from the temp table
union
selecting * from the temp table

is the way relatively slow compared to an alternative way?

[quoted text, click to view]
Again, have you tried it and gotten incorrect results?

You are misunderstanding. We are _passing_ the min or max of the id to
the function, which does its stuff and returns the value resulting from
dealing with the min or max of the resultset. See below:


[quoted text, click to view]

A function does not care if its argument is a column or not. Why are you
hung up on this. A function's argument is a value, whether it comes from
a column or is the result of a calculation is irrelevant.

[quoted text, click to view]

Yes, I believe I showed you the solution.
Why won't you believe it will work?

I cannot prove what I'm saying without a table or data. Wait, let's
construct something ...

create function dbo.calculate (
@id int) returns int as
begin
return (@id + 5)
end
go

select cast(1 as int) as id, 'a' as [name] into #temp
union all
select 2,'p'
union all
select 3,'p'
union all
select 4,'m'

select 0 as OrderBy, max(id) as id,
dbo.calculate(max(id)) as [Calculate Result] from #temp
union all
select 1, min(id),
dbo.calculate(min(id)) from #temp
union all
select 2, id,
dbo.calculate(id) from #temp
ORDER BY OrderBy, id

drop function dbo.calculate
drop table #temp

Try this and see if it gives you what you want.



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[quoted text, click to view]

what other way could I use to transmit the min and max values for each
column?

[quoted text, click to view]

Oh! you wanted the min and max of the _result _ of the calculation. This
is why example data and desired results are so important. We just wasted
all day on this when I could have answered it in my first post ...
[quoted text, click to view]

It may or may not be. I tend to avoid techniques that may involve IO
such as temp tables. An alternative would be a table variable. However,
table variables can involve IO as well.You will need to test your method
compared to both using a table variable and using a derived table which
I will illustrate here using the example I whipped up earlier:

create function dbo.calculate (
@id int) returns int as
begin
return @id * -1
end
go

select cast(1 as int) as id, 'a' as [name] into testvalues
union all
select 2,'p'
union all
select 3,'p'
union all
select 4,'m'
go
create view MinMax as
select min(calc) as mincalc, max(calc) as maxcalc
from (select dbo.calculate(id) as calc from testvalues) q
go

select 0 as OrderBy, id,[name],
mincalc [Calculate Result] from testvalues t
inner join MinMax m on dbo.calculate(id)=mincalc
union all
select 1, id,[name],
maxcalc from testvalues t
inner join MinMax m on dbo.calculate(id)=maxcalc
union all
select 2, id,[name],
dbo.calculate(id) from testvalues
ORDER BY OrderBy, id

drop function dbo.calculate
drop table testvalues
drop view minmax

One thing that is impairing the efficiency of this is your requirement
that the min and max results need to be in their own rows, requiring the
use of a union query.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[quoted text, click to view]

It depends on your requirements.
You could pass them back as output parameters.
You could include them as extra fields in the recordset being returned ...
yes it would increase the size of the recordset but you would avoid having
to run 3 separate queries and unioning the results.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"