SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t(id INT, grp VARCHAR(100), d DATE, val VARCHAR(100));

INSERT INTO t(id, grp, d, val)
VALUES (1, 'grpA', '2021-10-16', 'b')
      ,(2, 'grpA', '2021-10-16', 'a')
      ,(3, 'grpA', '2021-10-15', 'c')
      ,(4, 'grpA', '2021-10-14', 'd')
      ,(5, 'grpB', '2021-10-13', 'a')
      ,(6, 'grpB', '2021-10-13', 'g')
      ,(7, 'grpB', '2021-10-12', 'h');
7 rows affected

SELECT * FROM t ORDER BY id;


id
grp
d
val


1
grpA
2021-10-16
b

2
grpA
2021-10-16
a

3
grpA
2021-10-15
c

4
grpA
2021-10-14
d

5
grpB
2021-10-13
a

6
grpB
2021-10-13
g

7
grpB
2021-10-12
h


SELECT * 
FROM (
   SELECT t.*, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY d DESC) AS rn
   FROM t) sub
WHERE sub.rn = 1 AND sub.val = 'a'


id
grp
d
val
rn


5
grpB
2021-10-13
a
1


SELECT * 
FROM (
   SELECT t.*, COUNT(*) OVER(PARTITION BY grp, d ) AS cnt
   FROM t) sub
WHERE cnt > 1


id
grp
d
val
cnt


1
grpA
2021-10-16
b
2

2
grpA
2021-10-16
a
2

5
grpB
2021-10-13
a
2

6
grpB
2021-10-13
g
2

id	grp	d	val
1	grpA	2021-10-16	b
2	grpA	2021-10-16	a
3	grpA	2021-10-15	c
4	grpA	2021-10-14	d
5	grpB	2021-10-13	a
6	grpB	2021-10-13	g
7	grpB	2021-10-12	h