SQL Get lowest two values with QUALIFY

Question

I need to get lowest (earliest) 2 Dates after a Test Date based on join to the Test Table. I've tried running a QUALIFY-ROW_NUMBER-PARTITION, which seemed pretty straight forward in examples but it's not playing nice with my query. Everything before the QUALIFY works. I'm not implementing the QUALIFY properly or I should just being using another method.

SELECT p.[ENTITY_ID]
,[PROD_DATE]
,[WTR]
,[WCNT]
,[DAYS]
,t.max_TEST_DATE
,wt.TEST_DATE
,wt.TEST_VOL 
FROM (SELECT [ENTITY_ID]
,max([TEST_DATE]) as max_TEST_DATE
FROM [dbo].[PDEN_TEST]
group by ENTITY_ID) as t 
INNER JOIN [dbo].[PDEN_WELL_TEST] wt
on t.max_TEST_DATE = wt.TEST_DATE and t.ENTITY_ID = wt.ENTITY_ID
inner join [dbo].[PDEN_PROD] p
on p.ENTITY_ID = t.ENTITY_ID
where PROD_DATE >= TEST_DATE
qualify 
row_number() over (partition by p.ENTITY_ID, [PROD_DATE]) <=2

Modified as follows thanks to Gordon but I'm only getting first occurrence of each Entity_ID instead of 2

SELECT top (2) with ties p.[ENTITY_ID]
,[PROD_DATE]
,[WTR]
,[WCNT]
,[DAYS]
,t.max_TEST_DATE
,wt.TEST_DATE
,wt.TEST_VOL as IP24
FROM (SELECT [ENTITY_ID]
,max([TEST_DATE]) as max_TEST_DATE
FROM [DI].[dbo].[PDEN_WELL_TEST]
group by ENTITY_ID) as t 
INNER JOIN [DI].[dbo].[PDEN_WELL_TEST] wt
on t.max_TEST_DATE = wt.TEST_DATE and t.ENTITY_ID = wt.ENTITY_ID
inner join [DI].[dbo].[PDEN_PROD] p
on p.ENTITY_ID = t.ENTITY_ID
where PROD_DATE >= TEST_DATE
ORDER BY row_number() OVER (partition BY p.ENTITY_ID ORDER BY PROD_DATE)

ENTITY_ID PROD_DATE WTR WCNT DAYS max_TEST_DATE TEST_DATE IP24 60916 1998-12-01 1395 1 31 1998-11-21 1998-11-21 160 60919 1997-11-01 242 1 30 1997-10-10 1997-10-10 9776 60920 1993-04-01 50710 1 30 1993-04-01 1993-04-01 173 60921 1994-07-01 8300 1 14 1994-06-26 1994-06-26 0 60928 2017-04-01 38733 1 30 2017-03-14 2017-03-14 232 60926 2017-06-01 20379 1 20 2017-05-08 2017-05-08 29 60929 2001-07-01 8288 1 31 2001-06-09 2001-06-09 68

I changed the database tag to "teradata". SQL Server doesn't support qualify (as far as I know). — Gordon Linoff
As Gordon wrote, there's no QUALIFY in SQL Server, you need to wrap the ROW_NUMBER in a CTE or Dervied Table and move the condition to the outer WHERE. — dnoeth

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-03-01T00:38:21

I think you just need the order by part:

qualify row_number() over (partition by p.ENTITY_ID order by PROD_DATE) <= 2

If you do need this in SQL Server, you can do:

select top (2) with ties . . .
from . . .
. . .
order by row_number() over (partition by p.ENTITY_ID order by PROD_DATE)

This does the same thing in SQL Server.

SQL Get lowest two values with QUALIFY

1 Answers