tsql to select values from 2 specific rows

I need to select values from rows 1 and 5.

Close Price values can be listed in order as follows:

SELECT TOP 8 QuoteDate,ClosePrice FROM StockHist WHERE SymbolID=11817 ORDER BY QuoteDate DESC

QuoteDate                              ClosePrice
2014-01-03 00:00:00.000      19.06    --ClosePrice1
2014-01-02 00:00:00.000      19.05
2013-12-31 00:00:00.000      19.25
2013-12-30 00:00:00.000      19.29
2013-12-27 00:00:00.000      18.90    -- ClosePrice5
2013-12-26 00:00:00.000      18.47
2013-12-24 00:00:00.000      18.80
2013-12-23 00:00:00.000      18.30

How do I select ClosePrice1 minus ClosePrice5?

Thanks
DovbermanAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Something like this perhaps:
;WITH    CTE
          AS (
              SELECT TOP 8
                        QuoteDate,
                        ClosePrice,
                        ROW_NUMBER() OVER (ORDER BY QuoteDate DESC) Row
              FROM      StockHist
              WHERE     SymbolID = 11817
              ORDER BY  QuoteDate DESC
             )
    SELECT  QuoteDate,
            ClosePrice
    FROM    CTE
    WHERE   Row = 5

Open in new window

0
 
DovbermanAuthor Commented:
I can output row numbers as follows:


SELECT TOP 8 QuoteDate,SymbolID, ClosePrice, ROW_NUMBER() OVER (ORDER BY QuoteDate DESC) AS "RowNum"
FROM StockHist WHERE SymbolID = 11818
                                                                     ClosePrice RowNum
2014-01-03 00:00:00.000      11818      23.50      1
2014-01-02 00:00:00.000      11818      23.74      2
2013-12-31 00:00:00.000      11818      23.80      3
2013-12-30 00:00:00.000      11818      22.50      4
2013-12-27 00:00:00.000      11818      21.82      5
2013-12-26 00:00:00.000      11818      21.52      6
2013-12-24 00:00:00.000      11818      21.62      7
2013-12-23 00:00:00.000      11818      21.42      8

How can I select ClosePrice where RowNum = 5?

Thanks,
0
 
DovbermanAuthor Commented:
Another possibility.

How can I select the 5th row of a recordset?

SELECT TOP 8 QuoteDate,ClosePrice FROM StockHist WHERE SymbolID=11817 ORDER BY QuoteDate DESC

Need to SELECT only Row 5.
0
 
DovbermanAuthor Commented:
That will probably work.

I just tested this:

(SELECT QuoteDate FROM
(SELECT ROW_NUMBER() OVER (ORDER BY QuoteDate DESC ) AS RowNum, * FROM DownLoadDates WHERE MarketID=1 ) sub
WHERE RowNum = 5 )

Returns the QuoteDate from Row 5.
0
 
DovbermanAuthor 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.