Oracle 11g Release 2

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