SQL Server Join Queries using Rank and Partition

Question

I want one table of the highest BID and lowest ASK (price) for each EntityCode in the db.

The following two sets of code return two result sets I but cannot yet find/figure out how to join them:

Get highest Bid

(SELECT *   FROM    
   (SELECT 
        Contracts.Orders2Buy.EntityCode,
        Contracts.Orders2Buy.Bid,       
        Contracts.Orders2Buy.NumContractsStillAvailable,
        Contracts.Orders2Buy.TimePlaced,
        RANK() OVER (PARTITION BY EntityCode  
               ORDER BY Contracts.Orders2Buy.Bid DESC, 
               Contracts.Orders2Buy.TimePlaced DESC) AS Rank
    FROM
        Contracts.Orders2Buy
    WHERE
        Contracts.Orders2Buy.NumContractsStillAvailable > 0) AS temp
WHERE temp.Rank = 1)

Get Lowest Ask

(SELECT *   FROM    
(   SELECT 
        Contracts.Orders2Sell.EntityCode,
        Contracts.Orders2Sell.Ask,          
        Contracts.Orders2Sell.NumContractsStillAvailable AS AskVolume,
        Contracts.Orders2Sell.TimePlaced,
        RANK () OVER (  PARTITION BY EntityCode     
                ORDER BY    Contracts.Orders2Sell.Ask ASC, 
                Contracts.Orders2Sell.TimePlaced DESC) AS Rank
    FROM
        Contracts.Orders2Sell
    WHERE
        Contracts.Orders2Sell.NumContractsStillAvailable > 0) AS temp2
WHERE temp2.Rank = 1)

This post went from cte vs table variable performance to something about sql server cost vs web server cost? — dfundako, Oct 25 '16 at 19:52
My advice 1) ask a specific question. 2) post your relevant code. — RBarryYoung, Oct 25 '16 at 20:37

score 0 · Answer 1 · answered Oct 25 '16 at 21:03

Instead of UNION, use JOIN to combine three result sets into one result.

SELECT  *
FROM    highestBid hb
        JOIN lowestAsk la ON hb.stock_symbol = la.stock_symbol
        JOIN mostRecent mr ON la.stock_symbol = mr.stock_symbol

put the fields you need from each set in place of *

score 0 · Answer 2 · answered Oct 26 '16 at 21:04

0

What I sought to accomplish requires joining the code blocks above as follows:

SELECT * FROM 

      (Code block 1) t1

INNER JOIN

      (Code block 2) t2

ON t1.EntityCode = t2.EntityCode

answered Oct 26 '16 at 21:04

Mike S

157
3
13

SQL Server Join Queries using Rank and Partition

2 Answers2