Solved

tsql to select values from 2 specific rows

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

724 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