By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table my_table (Id int, Name varchar(10), Brand int, datee Date);
insert into my_table(Id, Name, Brand, datee) values (1, 'ABC', 1, to_date('2019-09-18', 'yyyy-mm-dd'));
1 rows affected
insert into my_table(Id, Name, Brand, datee) values (2, 'XYZ', 1, to_date('2019-09-19', 'yyyy-mm-dd'));
1 rows affected
insert into my_table(Id, Name, Brand, datee) values (3, 'MNO', 1, to_date('2019-09-18', 'yyyy-mm-dd'));
1 rows affected
insert into my_table(Id, Name, Brand, datee) values (4, 'ABC', 1, to_date('2019-09-19', 'yyyy-mm-dd'));
1 rows affected
insert into my_table(Id, Name, Brand, datee) values (5, 'PQR', 2, to_date('2019-06-17', 'yyyy-mm-dd'));
1 rows affected
insert into my_table(Id, Name, Brand, datee) values (6, 'MNO', 1, to_date('2019-03-19', 'yyyy-mm-dd'));
1 rows affected
insert into my_table(Id, Name, Brand, datee) values (7, 'ABC', 2, to_date('2019-09-19', 'yyyy-mm-dd'));
1 rows affected
select
name,
count(*) counter,
max(brand) brand,
max(trunc(datee)) maxdate
from my_table
where brand = 1
group by name
order by maxdate desc, counter desc
| NAME | COUNTER | BRAND | MAXDATE |
|---|---|---|---|
| ABC | 2 | 1 | 19-SEP-19 |
| XYZ | 1 | 1 | 19-SEP-19 |
| MNO | 2 | 1 | 18-SEP-19 |