Top two days select for multiple rows?

Question

I have a table with stock prices such as this:

I would like to do a SQL statement to get current latest price and the price before for a given stock ticker.

I can do that individually per stock, something like this:

  SELECT TOP 2 * FROM StockPrices WHERE TickerId = 'XASX:360' ORDER BY Date DESC

But for obvious reasons, this wouldn't work when there are multiple tickers.

I need something like this:

| TickerId  | Current CLOSE Price | Previous CLOSE Price |
| XASX:360  | 7.01                | 7.03                 |
| XASX:A200 | 123.92              | 123                  |
|  ....  etc .......                                     |
| XASX:AAC  | 1.38                | 1.365                |

An alternative would to even get just the latest date and the previous date.

| TickerId  | Current Trade Date | Previous Trade Date |
| XASX:360  | 2021-07-09         | 2021-07-08          |
| XASX:A200 | 2021-07-09         | 2021-07-08          |
| XASX:A2M  | 2021-07-09         | 2021-07-08          |
|  ....  etc .......                                   |

Notes about the data:

The max date would the "current date" - but it doesn't mean it is today as weekends and holidays are not trading days and won't have prices against it.
Previous Date is not CurrentDate - 1 for example, as it explained above the previous day could be a holiday or weekend.

Any suggestions or ideas would be appreciated.

user_1144035 · Answer

Your desired result set has the prices in separate columns rather than separate rows. That suggests conditional aggregation:

SELECT TICKERID,
       MAX(CASE WHEN SEQNUM = 1 THEN CLOSEPRICE END) as CURRENT_CLOSEPRICE,
       MAX(CASE WHEN SEQNUM = 2 THEN CLOSEPRICE END) as  PREVIOUS_CLOSEPRICE,
       MAX(TICKERDATE) AS CURRENT_TRADEDATE,
       MIN(TICKERDATE) AS PREVIOUS_TRADEDATE
FROM (SELECT SP.*,
             ROW_NUMBER() OVER (PARTITION BY TICKERID ORDER BY TICKERDATE DESC) AS SEQNUM
      FROM STOCKPRICES SP
     ) SP
WHERE SEQNUM <= 2
GROUP BY TICKERID;

Then for performance, you can add an index on (TICKERID, TICKERDATE DESC).

user_16280405 · Answer

You can achieve you result using LEAD function in MS SQL SERVER. using following query just need to replace your column name from your table.     

SELECT 
            TICKERID,
            CURRENTCLOSEPRICE,
            PREVIOUSCLOSEPRICE 
            FROM ( 
                    SELECT 
                        ROW_NUMBER() OVER(PARTITION BY TICKERID ORDER BY TICKERDATE DESC) AS SR,    
                        TICKERID,
                        CLOSEPRICE AS CURRENTCLOSEPRICE,
                        LEAD(CLOSEPRICE, 1) OVER(PARTITION BY TICKERID ORDER BY TICKERDATE DESC) AS PREVIOUSCLOSEPRICE
                        FROM STOCKPRICES 
            ) AS TBL 
    WHERE SR=1

Top two days select for multiple rows?

2 Answers