select rowcount per group before value in group = null

I need to select the top 3 rows per each group (xID -- order by xID). No
problem here. But I need to select the count of rows in each group before a
null value is encountered in the stat column -- ordering by xID

CREATE TABLE #tmp1(rowID int Identity(1,1), xID int, x1 int, stat varchar(1))

INSERT INTO #tmp1
SELECT 1, 1, 'f'
UNION ALL SELECT 1, 2, 'f'
UNION ALL SELECT 1, 3, 'f'
UNION ALL SELECT 1, 4, 'f'
UNION ALL SELECT 2, 1, 'f'
UNION ALL SELECT 2, 2, null
UNION ALL SELECT 2, 3, 'f'
UNION ALL SELECT 2, 4, 'f'
UNION ALL SELECT 3, 1, 'f'
UNION ALL SELECT 3, 2, 'f'
UNION ALL SELECT 3, 3, null

Here is what I have for getting the top 3 rows per group:

SELECT * FROM #tmp1 t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 3 * FROM #tmp1 t2 WHERE t2.xId = t1.xId ORDER BY
t2.xid) t3 WHERE t3.rowID = t1.rowID). This returns:


rowID xID x1 stat
1 1 1 f
2 1 2 f
3 1 3 f
5 2 1 f
6 2 2 NULL
7 2 3 f
9 3 1 f
10 3 2 f
11 3 3 NULL

From here I need to get this output

xID rowcount stat
1 3 f
2 1 f
3 2 f

xID = 1 had 3 rows (no null values encountered)
xID = 2 had 1 row before encountering a null
xID = 3 had 2 rows before encountering a null

Is it possible to get this output from the source data without looping?

Thanks,
Rich

SELECT A.xID, MAX(A.rownum), A.stat
FROM (SELECT *,
row_number() over (PARTITION BY xID
ORDER BY xID, rowID) as rownum
FROM #tmp1) as A
JOIN (SELECT *,
row_number() over (PARTITION BY xID
ORDER BY xID, rowID) as rownum
FROM #tmp1
WHERE stat IS NOT NULL) as B
ON A.rowID = B.rowID
AND A.rownum = B.rownum
WHERE A.rownum <= 3
GROUP BY A.xid, A.stat

xID stat
----------- -------------------- ----
1 3 f
2 1 f
3 2 f

The idea is that we number the rows once including the NULLs, and once
without the NULLs, and only take the ones where the numbers match.

Roy Harvey
Beacon Falls, CT


On Tue, 22 Jul 2008 08:38:01 -0700, Rich
wrote:

>I need to select the top 3 rows per each group (xID -- order by xID). No
>problem here. But I need to select the count of rows in each group before a
>null value is encountered in the stat column -- ordering by xID
>
>CREATE TABLE #tmp1(rowID int Identity(1,1), xID int, x1 int, stat varchar(1))
>
>INSERT INTO #tmp1
>SELECT 1, 1, 'f'
>UNION ALL SELECT 1, 2, 'f'
>UNION ALL SELECT 1, 3, 'f'
>UNION ALL SELECT 1, 4, 'f'
>UNION ALL SELECT 2, 1, 'f'
>UNION ALL SELECT 2, 2, null
>UNION ALL SELECT 2, 3, 'f'
>UNION ALL SELECT 2, 4, 'f'
>UNION ALL SELECT 3, 1, 'f'
>UNION ALL SELECT 3, 2, 'f'
>UNION ALL SELECT 3, 3, null
>
>Here is what I have for getting the top 3 rows per group:
>
>SELECT * FROM #tmp1 t1 WHERE EXISTS
>(SELECT * FROM (SELECT TOP 3 * FROM #tmp1 t2 WHERE t2.xId = t1.xId ORDER BY
>t2.xid) t3 WHERE t3.rowID = t1.rowID). This returns:
>
>
>rowID xID x1 stat
>1 1 1 f
>2 1 2 f
>3 1 3 f
>5 2 1 f
>6 2 2 NULL
>7 2 3 f
>9 3 1 f
>10 3 2 f
>11 3 3 NULL
>
>From here I need to get this output
>
>xID rowcount stat
>1 3 f
>2 1 f
>3 2 f
>
>xID = 1 had 3 rows (no null values encountered)
>xID = 2 had 1 row before encountering a null
>xID = 3 had 2 rows before encountering a null
>
>Is it possible to get this output from the source data without looping?
>
>Thanks,
>Rich