Solved

tsql to select values from 2 specific rows

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DATETIMEOFFSET feature 1 33
SQL compatability in SQL 2016 2 31
MS SQL Inner Join - Multiple Join Parameters 2 19
Sql Query with datetime 3 12
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now