?
Solved

tsql to select values from 2 specific rows

Posted on 2014-01-04
5
Medium Priority
?
347 Views
Last Modified: 2014-01-04
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
0
Comment
Question by:Dovberman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Author Comment

by:Dovberman
ID: 39756680
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
 

Author Comment

by:Dovberman
ID: 39756694
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1200 total points
ID: 39756747
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
 

Author Comment

by:Dovberman
ID: 39756849
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
 

Author Closing Comment

by:Dovberman
ID: 39756851
Thanks.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question