CODEWITHSUNDEEP
sqlsql-servert-sqlsubquery
StevenLightning
StevenLightning

Reputation: 31

How to avoid rerunning a subquery used multiple times?

The following is a simplified example of a problem I'm running into. Assume I have a query "SomeQuery" (SELECT... FROM... WHERE...) that gives an output that looks like this:

Status MyDate
A 6/14/2021
A 6/12/2021
B 6/10/2021
A 6/8/2021
B 6/6/2021
A 6/4/2021

I need to get the earliest status A date that is greater than the maximum status B date. In this case 6/12/2021.

I have a query that looks like this:

SELECT
  MIN(MyDate) AS DateNeeded
FROM
  SomeQuery
WHERE
  Status = 'A'
  AND MyDate > (
    SELECT
      MAX(MyDate) AS MaxDateB
    FROM
      SomeQuery
    WHERE
      Status = 'B'
  )

This works, but I would like to avoid running the subquery twice. I tried assigning an alias to the first instance of the subquery, and then using that alias in place of the second instance, but this results in an "Invalid object name" error.

Any help would be appreciated. Thanks.

Upvotes: 1

Views: 1021

Answers (2)

Moe Sisko
Moe Sisko

Reputation: 12005

I'm not sure I understand what you're after exactly, but it is possible to do something like this:

;WITH cte (MaxMyDate) as
(
   SELECT
      MAX(MyDate) AS MaxDateB
    FROM
      SomeQuery
    WHERE
      Status = 'B'    
)
SELECT
  MIN(MyDate) AS DateNeeded
FROM
  SomeQuery
WHERE
  Status = 'A'
  AND MyDate > (SELECT MaxMyDate from cte)

Some may find this a bit easier to read, since some of the complexity is moved to a cte.

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

but to avoid hitting table twice you could use window function:

select top(1) Mydate from 
(
 select *, max(case when Status = 'B' then Mydate end) over () MaxBDate from data 
) t 
where status = 'A'
and MyDate > MaxBDate
order by Mydate

db<>fiddle here

Upvotes: 1

Related Questions