SQLite Forum

Getting Minimum, Maximum and Current Weight
Login

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?