Alternate SQL Server query by performance?

Steffy Alen

4 years ago

Query which I am using:

select SUM(marks) 
from Table1 
where name = ? 
  and Date = (select top 1 Date 
              from Table1 
              where name =? 
                and Date < ? 
              order by Date desc)

Table1:

id	name	marks	Date
1	abc	34	01/01/2021
2	abc	15	05/01/2021
3	abc	20	05/01/2021
4	def	34	05/01/2021
5	abc	12	10/01/2021

select sum(marks) 
from Table1 
where name ='abc' 
  and Date = (select top 1 Date 
              from Table1 
              where name = 'abc' 
                 and Date < 10/01/2021 
              order by Date desc)

Result 35