Change SQL query: Partition By-CodePudding

I have a TABLE, for that TABLE I want to retrieve table data for max of DATE<01-01-2021 & max of DATE<01-01-2022.

Table Query is:

  select * from TABLE where ENTITY ID=47 order by DATE

TABLE DATA:

AuditID	DATE	ENTITY ID
78632	11/14/2017	47
82181	12/12/2017	47
110243	7/6/2018	47
134478	4/24/2019	47
154082	8/19/2019	47
154083	4/3/2021	47
154084	5/3/2021	47
222971	6/3/2021	47

Result required:

AuditID	DATE	ENTITY ID
154082	8/19/2019	47
222971	6/3/2021	47

For the above result I've used below query:

select * from TABLE where DATE =
(select max(DATE) from TABLE where DATE <DATEFROMPARTS(YEAR(GETDATE())-1,1,1) and ENTITY ID=47 )
OR DATE =
(select max(DATE) from TABLE where DATE < DATEFROMPARTS(YEAR(GETDATE()),1,1) and ENTITY ID=47 )
and ENTITY ID=47

But the due to performance issue for millions of records, I want to write above query using "OVER PARTITION BY "

CodePudding user response：

You can use top and union. If date needs to be calculated do it before the query. ie:

declare @thisYear datetime = datefromparts(year(getdate()),1,1);
declare @lastYear datetime = datefromparts(year(getdate())-1,1,1);
declare @entityId int = 47;

select * from
    (select top(1) * from myTable
    where entitiyId = @entityId and [date] < @lastYear
    order by [date] desc) t1
    union all
select * from
    (select top(1) * from myTable
    where entitiyId = @entityId and [date] < @thisYear
    order by [date] desc) t2;

CodePudding user response：

I'm pretty sure a union all would be far more efficient than those subqueries:

DECLARE @EntityID int = 47,
        @thisyear date = DATEFROMPARTS(YEAR(GETDATE()),1,1);

SELECT * FROM 
(
  SELECT TOP (1) * FROM dbo.table 
  WHERE DATE < @thisyear
    AND EntityID = @EntityID
  ORDER BY DATE DESC
) AS t2021
UNION ALL
SELECT * FROM 
(
  SELECT TOP (1) * FROM dbo.table
  WHERE DATE < DATEADD(YEAR, -1, @thisyear)
    AND EntityID = @EntityID
  ORDER BY DATE DESC
) AS t2020;

If you really, really, really want to use a window function:

DECLARE @EntityID int = 47,
        @thisyear date = DATEFROMPARTS(YEAR(GETDATE()),1,1);

; /* previous statement terminator, see sqlblog.org/cte */
WITH cte AS
(
  SELECT AuditID, DATE, EntityID, y = YEAR([Date]),
    rn = ROW_NUMBER() OVER (PARTITION BY YEAR([Date]) ORDER BY [Date] DESC)
  FROM dbo.table
  WHERE EntityID = @EntityID
    AND DATE < @thisyear
)
SELECT TOP (2) AuditID, DATE, EntityID
   FROM src 
   WHERE rn = 1 ORDER BY y DESC;

CodePudding user response：

Though the union all from the other answers is IMHO the most easy to read and understand solution, I would still like to offer another way to write it

select t.*
from   table1 t
  cross apply (select top 1 [Date]
               from   table1 t2
               where  t2.[Date] < '20210101'
               order by t2.[Date] desc
              ) m21
  cross apply (select top 1 [Date]
               from   table1 t2
               where  t2.[Date] < '20220101'
               order by t2.[Date] desc
              ) m22
where  t.[Date] in (m21.[Date], m22.[Date])
and    t.EntityID = 47

You can see it in this DBFiddle