asked on

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CAMPzxzxDeathzxzx

ASKER

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

CAMPzxzxDeathzxzx

ASKER

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

CAMPzxzxDeathzxzx

ASKER

Thanks