Using SQLite3 how can I get the last row which has a specific value? For a couple of possible values-CodePudding

I'd like to query a table in order to create a summary, as follows:

The data table:

The output should be:

The queries to create the table are here:

CREATE TABLE Example (
    Category TEXT,
    Monthly TEXT,
    Value INTEGER
);

INSERT INTO Example VALUES ("A", "Jan", 45);
INSERT INTO Example VALUES ("B", "Jan", 23);
INSERT INTO Example VALUES ("A", "Feb", 35);
INSERT INTO Example VALUES ("B", "Feb", 54);
INSERT INTO Example VALUES ("A", "Mar", 34);
INSERT INTO Example VALUES ("A", "Apr", 75);
INSERT INTO Example VALUES ("B", "Apr", 4);
INSERT INTO Example VALUES ("A", "May", 8);

I'm stuck on how to accomplish this, I came with the following query, but it is not working as it should.

SELECT
    e.Category ,
    MAX(e.Monthly) , -- WRONG! need to fix it
    e.Monthly ,
    MAX(e.Value)
FROM
    Example e 
GROUP BY
    e.Category ;

How should I write this query correctly? I'm using a SQLite3 database.

CodePudding user response：

Note, use single quotes for strings and double quotes for identifiers. Do not use double quotes for strings.

WITH
    months(Monthly, ord) AS (
        VALUES
            ('jan', 1), ('feb', 2), ('mar', 3),
            ('apr', 4), ('may', 5), ('jun', 6), 
            ('jul', 7), ('aug', 8), ('sep', 9),
            ('oct', 10), ('nov', 11), ('dec', 12)
    ),
    ExampleEx AS (
        SELECT Example.*, months.ord
        FROM Example, months
        WHERE lower(Example.Monthly) = months.Monthly
    ),
    latest AS (
        SELECT Category, Monthly AS "Latest month", max(ord) AS ord
        FROM ExampleEx
        GROUP BY Category
    ),
    best AS (
        SELECT Category, Monthly AS "Best month", max(Value) AS "Best value"
        FROM Example
        GROUP BY Category
    )
SELECT latest.Category, latest."Latest month", best."Best month", best."Best value"
FROM latest, best
WHERE latest.Category = best.Category;

CodePudding user response：

Refer query below -

with data as (
  select
    category,
    monthly monthly1,
    (select monthly from example e1 
        where e1.category = e.category 
                AND e1.value = 
                    (select max(value) from example e2 
                        where e2.category = e1.category)) monthly2,
    value 
  from example e)
    select category,
        max(monthly1) Latest_month,
        max(monthly2) Best_month,
        max(value) value
     from data
     group by category;

It needs date manipulation related to month names though.

Refer fiddle here for details.