filter data by row number

[AtCor]

SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] <= 89) AS a
WHERE RowNum <= 4
UNION
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] >= 90) AS a
WHERE RowNum <= 2

This query returns values above 90 (I need 2 of them) or values between 80 and 89 (data is already filtered for only greater >=80) and I need 4 values above 80. I only need either 2 above 90 or 4 above 80, not both, and this query returns 2 above 90, but also the values between 80 and 89. If there are already 2 above 90, I do not want any values between 80 and 89. If there are 4 above 80, I do not need any additional values. If the are two above 80 and 1 above 90, I will take all of them (max I will ever take is 4).

[iburyak]

Can you give me sample data to work on?

[AtCor]

Patient Identifier Patient Initials Date Time Operator Index
0517_00003 GHV 18-Oct-06 11:48 91
0517_00003 GHV 18-Oct-06 11:50 100
0517_00004 JMH 17-Oct-06 11:41 89
0517_00004 JMH 17-Oct-06 11:50 93
0517_00004 JMH 17-Oct-06 11:52 91
0517_00004 JMH 17-Oct-06 12:00 93
0534_00003 JS 21-Nov-06 12:35 100
0534_00003 JS 21-Nov-06 12:46 100
0534_00004 ChM 20-Nov-06 10:49 100
0534_00004 ChM 20-Nov-06 10:51 100
0534_00006 JK 4-Dec-06 9:38 100
0534_00006 JK 4-Dec-06 9:47 84
0534_00006 JK 4-Dec-06 9:50 93
0534_00007 TL 29-Nov-06 9:22 98
0534_00007 TL 29-Nov-06 9:34 100
0539_00001 PGL 9-Oct-06 9:39 100
0539_00001 PGL 9-Oct-06 9:43 95
0539_00002 DWR 27-Oct-06 10:04 91
0539_00002 DWR 31-Oct-06 11:40 92
0539_00002 DWR 31-Oct-06 11:41 96
0539_00002 DWR 31-Oct-06 11:42 92
0539_00003 JmL 30-Nov-06 9:14 96
0539_00003 JmL 30-Nov-06 9:18 97

[AtCor]

I figured it out! Thanks!

[AtCor]

Here is the code I wrote and it is not correct although it appears to be correct at first. I was validating my data and discovered on several instances a value of 80 (something) is there instead of 90 (something).

SELECT [Patient Identifier], Date, [Operator Index], Time

FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] BETWEEN 80 AND 89
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
[First Step] AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]