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 |
