CODEWITHSUNDEEP
sql-servergroup-by
Dan
Dan

Reputation: 45752

Get the corresponding (not aggregated) value to an aggregated value

SELECT M.TradeDate, M.Expiry, M.Future
FROM MTM as M 
JOIN (SELECT TradeDate, MIN(Expiry) as Expiry
      FROM MTM
      WHERE Code = 'GOVI'
        and Type = 'F'
      GROUP BY TradeDate) as T 
ON M.TradeDate = T.TradeDate and M.Expiry = T.Expiry
WHERE M.Code = 'GOVI'
  and M.Type = 'F'
ORDER BY TradeDate

In this query, I want to extract a TradeDate, the nearest expiry (min) and the "Future" value that corresponds to that near expiry. This corresponding future means I now have to have a bloated nested query. The query does works perfectly. But I'm sure there must be a quicker (to develop, not necessarily to execute), clearer way to write it. Without the nested query and without repeating the exact same WHERE conditions. I thought maybe OVER could help but I can't see how. Is this really the only way to do it?

SQL is normally so declarative but I feel that in this form of query, which I encounter often, the query really doesn't read like the request you make yourself in your head.

Upvotes: 1

Views: 58

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

The self-join method is the old, expensive way to do it, and you'll find that its cost goes up exponentially as the table gets larger. Since 2005, we've been able to use window functions like ROW_NUMBER() to return grouped results along with the non-grouped data in that row. Here is how I would write your query:

;WITH m AS 
(
  SELECT TradeDate, Expiry, Future, rn = ROW_NUMBER() OVER 
   (PARTITION BY TradeDate ORDER BY Expiry) 
  FROM dbo.MTM 
  WHERE Code = 'GOVI' AND Type = 'F'
)
SELECT TradeDate, Expiry, Future 
  FROM m 
  WHERE rn = 1 
  ORDER BY Expiry;

Basically, this takes each unique TradeDate and applies a "rank" to each set (from 1-n) based on the value of Expiry.

Upvotes: 1

Related Questions