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?