filter data by row number

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).Can you give me sample data to work on?|||Patient IdentifierPatient InitialsDateTimeOperator Index
0517_00003GHV18-Oct-0611:4891
0517_00003GHV18-Oct-0611:50100
0517_00004JMH17-Oct-0611:4189
0517_00004JMH17-Oct-0611:5093
0517_00004JMH17-Oct-0611:5291
0517_00004JMH17-Oct-0612:0093
0534_00003JS21-Nov-0612:35100
0534_00003JS21-Nov-0612:46100
0534_00004ChM20-Nov-0610:49100
0534_00004ChM20-Nov-0610:51100
0534_00006JK4-Dec-069:38100
0534_00006JK4-Dec-069:4784
0534_00006JK4-Dec-069:5093
0534_00007TL29-Nov-069:2298
0534_00007TL29-Nov-069:34100
0539_00001PGL9-Oct-069:39100
0539_00001PGL9-Oct-069:4395
0539_00002DWR27-Oct-0610:0491
0539_00002DWR31-Oct-0611:4092
0539_00002DWR31-Oct-0611:4196
0539_00002DWR31-Oct-0611:4292
0539_00003JmL30-Nov-069:1496
0539_00003JmL30-Nov-069:1897|||I figured it out! Thanks!|||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]

Filter a Dataset with the Range (inc.) operator

It seems that when I use the Range(inc.) as the filter operator for a Dataset and then try to use the Last and First keywords to extract the results from the dataset in the layout the results are sometimes wrong and don't match the expected.

Does anyone knows of any issues that would affect this filtering mechanism?

That is because your results are not sorted by appropriate fields. If you want to use First(field1) and you have filtered on range of field1, then you must sort by that field to get correct values for first and last values.

Shyam

|||Thanks for you answer, although it was not exactly what I needed but
nevertheless I was able to resolve this problem.
The solution lies in not choosing setting as a field the one you want to filter over,
with the range operator.
I don't know if that makes any sense but it worked for me.sql