I have the following table
CREATE TABLE usages (
usage number not null,
date_recorded date not null,
category varchar2(100) not null
name varchar2(100) not null
);
usage | date_recored | category | name |
---|---|---|---|
10 | 2021-01-01 | Red | Bob |
15 | 2021-01-02 | Red | Bob |
8 | 2021-01-03 | Red | Bob |
24 | 2021-01-02 | Blue | Bob |
I want to group by the category and delete the row with the maximum date recorded. So my result would look like
usage | date_recored | category | name |
---|---|---|---|
8 | 2021-01-03 | Red | Bob |
24 | 2021-01-02 | Blue | Bob |
However, my result ends up essentially showing everything.
SELECT
usage, max(date_recorded), category, name
from usages
group by name, category, usages
When I do this:
SELECT
max(usage), max(date_recorded), category, name
from usages
group by name, category
It then shows me the result I want, expect the usage is 15, rather than 8.
usage | date_recored | category | name |
---|---|---|---|
15 | 2021-01-03 | Red | Bob |
24 | 2021-01-02 | Blue | Bob |