SQL to pull only the first 10 records of all fields in table, grouped by only 2 columns

Question

My SQL skills are too weak to solve this problem, but I am pretty certain it is possible to solve.

To simplify - I have a small table with 5 columns, let's label them A, B, C, D, E. It's 1000 rows.

I need to be able to group by columns A and B where (E is not null and E <> ''). That part I can do.

select T.A, T.B, count(*) as countAll
from TABLE T
where not T.E is null and T.E <> ''
group by T.A, T.B

But then I need to be able to get just the first 10 rows of each group of all the columns ([A-E]) included in each grouping within those parameters. This is where I'm flailing. What I need to see is all the fields in the table returned for the first 10 records of each grouping.

The below seems very similar to what I need but I so far cannot get it to even compile on my end. I must not be using the PARTITION BY clause correctly (never used it before). https://stackoverflow.com/a/51527260/3536926

SELECT MemberID, ResNumber, pcode, MemberEmail, arrivaldate,
FROM (
 SELECT MemberID, ResNumber, pcode, MemberEmail, arrivaldate,
    ROW_NUMBER () OVER w AS RN
  FROM sometable
  WINDOW w AS (PARTITION BY MemberID ORDER BY ResNumber ASC)
) X
WHERE RN <= 2
 

Maybe I should be using something besides GROUP BY like PARTITION BY but I'm not familiar with this?

Answer 1

I think this is perhaps just a syntax error. OVER clause is formatted like this:

SELECT MemberID, ResNumber, pcode, MemberEmail, arrivaldate
FROM (
 SELECT MemberID, ResNumber, pcode, MemberEmail, arrivaldate,
    ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY ResNumber ASC) AS RN
  FROM sometable
) X
WHERE RN <= 10