how to get min() and max() of a stored procedure"s results?

Hello,

Im stuck on this stored procedure. I want to get a recordset that has
the first two rows populated with the min() and max() of each column of
the following stored procedure, with the rest of the rows filled with
the results from the stored procedure:


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

desired result:
row1: min value for each column
row2: max value for each column
row3+: values from the above stored procedure

Re: how to get min() and max() of a stored procedure"s results?

mike wrote:
> Hello,
>
> Im stuck on this stored procedure. I want to get a recordset that has
> the first two rows populated with the min() and max() of each column
> of the following stored procedure, with the rest of the rows filled
> with the results from the stored procedure:
>
>
> SELECT
> id as '#',

Why use a non-standard name for the field name? When displaying the results,
you can put any text you want in the column heading.

> name,
> dbo.calculate(table_name.id) as 'Calculation Result'
> FROM
> table_name
>
> desired result:
> row1: min value for each column
> row2: max value for each column
> row3+: values from the above stored procedure

That's not a stored procedure: it's a SELECT statement.

What database (type and version) are you using?

For what you are asking, you will need to use a UNION query. If you are
using SQL Server, the following should work:

SELECT 0 as OrderBy,
min(id) as id,
min(name) as name,
dbo.calculate(min(table_name.id)) as 'Calculation Result'
FROM table_name
UNION ALL
SELECT 1,
max(id),
max(name),
dbo.calculate(max(table_name.id))
FROM table_name
UNION ALL
SELECT
2,
id,
name,
dbo.calculate(table_name.id)
FROM
table_name
ORDER BY OrderBY, id



--
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"

Re: how to get min() and max() of a stored procedure"s results?

> For what you are asking, you will need to use a UNION query. If you are
> using SQL Server, the following should work:
>
> SELECT 0 as OrderBy,
> min(id) as id,
> min(name) as name,
> dbo.calculate(min(table_name.id)) as 'Calculation Result'
> FROM table_name
> UNION ALL
> SELECT 1,
> max(id),
> max(name),
> dbo.calculate(max(table_name.id))

This is incorrect, isn't it? I am looking for the max value after the
calculate() function operates, not before. So I need max() of the
table of results produced by the SELECT statement below (which I
incorporated in a stored procedure already, hoping that I could have a
separate stored procedure that would get the min() and max() or the
result set from the original stored procedure). Do you know how to
get the max and min values after functions like calculate() operate?

Thanks,
Mike

> FROM table_name
> UNION ALL
> SELECT
> 2,
> id,
> name,
> dbo.calculate(table_name.id)
> FROM
> table_name
> ORDER BY OrderBY, id
>
>
>
> --
> 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"

Re: how to get min() and max() of a stored procedure"s results?

mike wrote:
>> For what you are asking, you will need to use a UNION query. If you
>> are using SQL Server, the following should work:
>>
>> SELECT 0 as OrderBy,
>> min(id) as id,
>> min(name) as name,
>> dbo.calculate(min(table_name.id)) as 'Calculation Result'
>> FROM table_name
>> UNION ALL
>> SELECT 1,
>> max(id),
>> max(name),
>> dbo.calculate(max(table_name.id))
>
> This is incorrect, isn't it?

Did you try it and get incorrect results?

> I am looking for the max value after the
> calculate() function operates, not before.

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?

> So I need max() of the
> table of results produced by the SELECT statement below (which I
> incorporated in a stored procedure already, hoping that I could have a
> separate stored procedure that would get the min() and max() or the
> result set from the original stored procedure). Do you know how to
> get the max and min values after functions like calculate() operate?

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.

Re: how to get min() and max() of a stored procedure"s results?

> 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?

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

Re: how to get min() and max() of a stored procedure"s results?

mike wrote:
>> 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?
>
> 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:
>
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:


> 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?

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.

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

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.

Re: how to get min() and max() of a stored procedure"s results?

Bob Barrows [MVP] wrote:
> Again, have you tried it and gotten incorrect results?

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?

Re: how to get min() and max() of a stored procedure"s results?

mike wrote:
> Bob Barrows [MVP] wrote:
>> Again, have you tried it and gotten incorrect results?
>
> 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

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 ...
>
> 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?

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.

Re: how to get min() and max() of a stored procedure"s results?

> 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.

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

Re: how to get min() and max() of a stored procedure"s results?

mike wrote:
>> 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.
>
> what other way could I use to transmit the min and max values for each
> column?

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"