Getting Minimum, Maximum and Current Weight
(1) By Cecil (CecilWesterhof) on 2021-04-12 13:10:38 [source]
One of my new years resolutions was that I wanted to loose significant weight. To help me with this I created the following table:
CREATE TABLE dayValues ( measureDate DATE DEFAULT CURRENT_DATE NOT NULL, weight REAL NOT NULL, PRIMARY KEY (measureDate) );
Interesting information would be when I first hit my lowest weight and last my highest weight.
For this I created the following query:
WITH vals AS ( SELECT MIN(weight) AS minimum , MAX(weight) AS maximum , DATE() AS today FROM dayValues ) SELECT 'Minimum' AS 'Type' , MIN(measureDate) AS 'Measure Date' , minimum AS 'Weight' FROM dayValues , vals WHERE weight = minimum UNION ALL SELECT 'Maximum' , MAX(measureDate) , maximum FROM dayValues , vals WHERE weight = maximum UNION ALL SELECT 'Today' , today , weight FROM dayValues , vals WHERE measureDate = today ;
Is that a reasonable query, or can it be improved upon?