Is this rewrite of a query OK

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Is this rewrite of a query OK

Cecil Westerhof-5
I had the following query:
SELECT MIN(totalUsed)                  AS minimum
,      MAX(totalUsed)                  AS maximum
,      MAX(totalUsed) - MIN(totalUsed) AS range
FROM   quotes

But I did not like it because I repeated the MIN and MAX. So I rewrote it
to the following:
SELECT Minimum
,      Maximum
,      Maximum - Minimum AS Range
FROM   (
    SELECT MIN(totalUsed) AS Minimum
    ,      MAX(totalUsed) AS Maximum
    FROM   quotes
)

Is this acceptable, or could there be unintended consequences? Is there a
better way to do it?

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this rewrite of a query OK

James K. Lowden
On Fri, 25 Jan 2019 23:35:51 +0100
Cecil Westerhof <[hidden email]> wrote:

> SELECT MIN(totalUsed)                  AS minimum
> ,      MAX(totalUsed)                  AS maximum
> ,      MAX(totalUsed) - MIN(totalUsed) AS range
> FROM   quotes
>
> But I did not like it because I repeated the MIN and MAX.

There's no problem with either version.  For what it's worth, I like
your first one, above.  It's shorter, and the meaning is not obscured
by calling min & max twice.  Computationally they should be the same;
the DBMS will traverse "quotes" just once.  

If the repeated calcuations were more complicated, to the point that
burying them in a subquery made the whole thing shorter, then I would
probably opt for something like the 2nd version,  to avoid the
possibility of errors in the repeated code.

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this rewrite of a query OK

Jens Alfke-2
In reply to this post by Cecil Westerhof-5


> On Jan 25, 2019, at 2:35 PM, Cecil Westerhof <[hidden email]> wrote:
>
> I had the following query:
> SELECT MIN(totalUsed)                  AS minimum
> ,      MAX(totalUsed)                  AS maximum
> ,      MAX(totalUsed) - MIN(totalUsed) AS range
> FROM   quotes

Couldn’t you do the subtraction in the program that’s running the query? (Or is this something you run directly from the shell?)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this rewrite of a query OK

Stephen Chrzanowski
I can see this query being useful as a part of an abstraction theory.  You
take away the responsibility of having a developer handle the math, so the
developer doesn't need to worry about what the two primary values are.
They can just pick what they need from the query.  Back in the Borland
Pascal days, when looking at drive space use and availability, I'd get
"frustrated" or slightly annoyed that I'd have to have a helper function or
code for the delta on storage.  IIRC, there is code to tell you the size of
the drive, and code to tell you the amount of used space, but, nothing
about what's free, or something along that line.  So a function, variable
or inline-math had to be written to give me the appropriate value.

BUT, I do also understand the theory that the DB engine (Of any flavor)
should be used to provide code the raw data, and the code should handle
presentation and manipulation.

Six in one basket, half dozen in the other, IMO.


On Fri, Jan 25, 2019 at 7:38 PM Jens Alfke <[hidden email]> wrote:

>
> Couldn’t you do the subtraction in the program that’s running the query?
> (Or is this something you run directly from the shell?)
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users