Solved

tsql to select values from 2 specific rows

Posted on 2014-01-04
5
345 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 300 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
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…
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…

730 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