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

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?

CodePudding user response：

Is the order of each set of returned rows important? This is a little janky but may get you close to what you're after.

WITH cte_Stuff
AS (
    SELECT t1.A, t1,B, t1.C, t1.D, t1.E, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowId --Returns a random set unless the ORDER BY is specified
    FROM [TABLE] t1
    INNER JOIN (
        SELECT t2.A, t2.B, COUNT(*) as countAll
        FROM [TABLE] t2
        WHERE ISNULL(t2.E, '') <> ''
        GROUP BY t2.A, t2.B
         ) x ON t1.A = t2.A
                AND t1.B = t2.B
)

SELECT c.A, c.B, c.C, c.D, c.E
FROM cte_Stuff c
WHERE c.RowId <= 10

CodePudding user response：

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