Re: Question about finding maximum and minimum

These sound like very unusual, and excessively restrictive, constraints. What has put you into such unusual, and severely restrictive, constraints? I really would like to know, this isn't a rhetorical question. Is it technical (the consequences of some particular design)? Because if it is, I would want to know what software puts this kind of constraint on development so that I may avoid it in the future, and so that I may help those I teach avoid it in the future (because it is excessive [and just plain wrong] to put such a constraint on a system). If it is political, I would understand if you just answer 'it is political' because some consulting houses prevent their clients from saying anything bad about them (or they will sue for defamation of character) [notice, I didn't say which ones] If this is part of a test for some class or course, then getting an answer here may be unethical.

anyway: here is how I would do it.

Since you cant change the first part of the statement, this means that the minimum and maximum cant be listed as separate columns. They will have to appear as two rows in the return set. You can limit the return to just the two rows that show the minimum and the maximum by adding this at the bottom:

and t1.a in
(select min(t1.a) from t t1
 union
 select max(t1.a) from t t1
)

Now, unless 'a' is indexed (with a non-unique index, of course) then this may run quite a while, if the number of rows is large.

thtsang_at_my-dejanews.com wrote:

> Hello,
>
> I have a silly question about SQL statements. I have the following SQL
> statement:
>
> select t1.a
> from t t1
> where t1.a >= a1 and
> t1.a <= a2
>
> where a1, a2 are 2 variables that are defined somewhere else, so that they
> cannot be used anywhere else in the SQL statement.
>
> However, I only need the maximum and minimum of the results. That is, the
> following SQL statement will suit my need:
>
> select max(t1.a), min(t1.a)
> from t t1
> where t1.a >= a1 and
> t1.a <= a2
>
> The problem is that I cannot change anything in the original SQL statement (I
> know this is silly but I am under such restriction.) What I can do is only
> adding something at the end. It can involve a sub-query. Also, what makes the
> problem challenging is that I cannot use a1 and a2 in the thing I add to the
> end.
>
> Please tell me how to do this, or whether it is impossible to do this in one
> SQL statement.
>
> Thank you very much
>
> Anthony
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Dec 07 1998 - 01:10:16 CST