How do you write partition over partition selecting top 5 records?

I have written this SP as a starting point for what I'm trying to do.  I have included a pic of the results from running it.

I need the results to be limited to the top 5 bid timestamp records for each symbol and I also need the top 5 ask timestamps for each symbol.  I'm sure the SP below needs to be partitioned but I don't know how to do it at 2 levels - ask and bid

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_LiveCoinGetRecentFiveAllOrderBook]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_LiveCoinGetRecentFiveAllOrderBook];
GO
CREATE PROCEDURE [dbo].[usp_LiveCoinGetRecentFiveAllOrderBook]
AS
BEGIN	
SELECT [Symbol], [BidQuantity], BidTimeStamp, [Bid], [AskQuantity], [Ask], AskTimeStamp
FROM (
	SELECT 
		x.[Symbol],
		b.[BidQuantity],
		b.[Bid],
		b.[TimeStamp] As 'BidTimeStamp',		
		c.[AskQuantity],
		c.[Ask],
		c.[TimeStamp] AS 'AskTimeStamp'	      
   FROM [dbo].[LiveCoinAllOrderBook] x
   Join [dbo].[LiveCoinAllOrderBookBids] b ON x.[LiveCoinAllOrderBookID] = b.[LiveCoinAllOrderBookID]
   Join [dbo].[LiveCoinAllOrderBookAsks] c ON x.[LiveCoinAllOrderBookID] = c.[LiveCoinAllOrderBookID] 
   GROUP By 
		x.[Symbol],
		b.[BidQuantity],
		b.[Bid],
		b.[TimeStamp],		
		c.[AskQuantity],
		c.[Ask],
		c.[TimeStamp] ) z 
	ORDER BY z.[Symbol] DESC
END
GO

Open in new window



results
CAMPzxzxDeathzxzxAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
Use apply operators instead of joins. These are good for "top n per group" needs such as this.
SELECT
    x.[Symbol]
  , b.[BidQuantity]
  , b.[Bid]
  , b.[TimeStamp] AS 'BidTimeStamp'
  , c.[AskQuantity]
  , c.[Ask]
  , c.[TimeStamp] AS 'AskTimeStamp'
FROM [dbo].[LiveCoinAllOrderBook] x
OUTER APPLY (
    SELECT TOP (5)
        BidQuantity
      , Bid
      , TimeStamp
    FROM [dbo].[LiveCoinAllOrderBookBids] b
    WHERE b.[LiveCoinAllOrderBookID] = x.[LiveCoinAllOrderBookID]
    ORDER BY TimeStamp DESC
    ) b
OUTER APPLY (
    SELECT TOP (5)
      AskQuantity
    , Ask
    , TimeStamp
    FROM [dbo].[LiveCoinAllOrderBookAsks] c
    WHERE c.[LiveCoinAllOrderBookID] = x.[LiveCoinAllOrderBookID]
    ORDER BY TimeStamp DESC
    ) c
ORDER BY
    x.[Symbol]
  , b.[TimeStamp]
  , c.[TimeStamp]

Open in new window

0
 
CAMPzxzxDeathzxzxAuthor Commented:
Same result.  Maybe I should get my intended results from two different SP and combining them in code?

Capture.JPG
0
 
CAMPzxzxDeathzxzxAuthor Commented:
I got it thanks to you!  I added Group By

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_LiveCoinGetRecentFiveAllOrderBook]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_LiveCoinGetRecentFiveAllOrderBook];
GO
CREATE PROCEDURE [dbo].[usp_LiveCoinGetRecentFiveAllOrderBook]
AS
BEGIN	
SELECT
    x.[Symbol]
  , b.[BidQuantity]
  , b.[Bid]
  , b.[TimeStamp] AS 'BidTimeStamp'
  , c.[AskQuantity]
  , c.[Ask]
  , c.[TimeStamp] AS 'AskTimeStamp'
FROM [dbo].[LiveCoinAllOrderBook] x
OUTER APPLY (
    SELECT TOP (5)
        BidQuantity
      , Bid
      , TimeStamp
    FROM [dbo].[LiveCoinAllOrderBookBids] b
    WHERE b.[LiveCoinAllOrderBookID] = x.[LiveCoinAllOrderBookID]
	ORDER BY TimeStamp DESC
    ) b
OUTER APPLY (
    SELECT TOP (5)
      AskQuantity
    , Ask
    , TimeStamp
    FROM [dbo].[LiveCoinAllOrderBookAsks] c
    WHERE c.[LiveCoinAllOrderBookID] = x.[LiveCoinAllOrderBookID]
	ORDER BY TimeStamp DESC
    ) c
	GROUP BY x.[Symbol], b.[BidQuantity], b.[Bid], b.[TimeStamp],c.[AskQuantity], c.[Ask], c.[TimeStamp]
ORDER BY 
    x.[Symbol]
  , b.[TimeStamp]
  , c.[TimeStamp]
END
GO

Open in new window

0
 
CAMPzxzxDeathzxzxAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.