Solved

Need SQL Statement that selects a column from the first and second row of a table

Posted on 2007-11-19
14
197 Views
Last Modified: 2010-03-20
I have a table that can be ordered by a date field. I need to return a field from the first and second row of the table.

Psuedocode:

Select (CurrentPrice from Row 1 -, CurrentPrice from Row 2) as PriceDiff  
From PriceHistoryTable
Where ProductID=1234
Order By PriceDate Desc

I need data only from rows 1 and 2.

QuoteDate                         Price
2007-11-16 00:00:00.000      24.58
2007-11-15 00:00:00.000      25.54  ' I need to return (24.58 - 25.54) as PriiceDiff
2007-11-14 00:00:00.000      26.00
2007-11-13 00:00:00.000      25.53

Can this be done ?
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
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20316070
what if there is only 1 row?
0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20316091
This can be done, sort of, using a sub-query you can get the next CurrentPrice from your table:

SELECT
      TOP 1
      CurrentPrice - (SELECT TOP 1 CurrentPrice FROM PriceHistoryTable WHERE ProductID=PHT.ProductID AND PriceDate < PHT.PriceDate ORDER BY PriceDate DESC) AS PriceDiff
FROM
      PriceHistoryTable PHT
WHERE
      PHT.ProductID=1234
ORDER BY
      PHT.PriceDate DESC
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20316122
SELECT price - prevprice
  FROM (SELECT   price, LEAD(price) OVER(PARTITION BY productid ORDER BY quote_date DESC) AS prevprice,
                 ROW_NUMBER() OVER(PARTITION BY productid ORDER BY quote_date DESC) rn
            FROM ee_t
           WHERE productid = 1234
        ORDER BY quote_date DESC)
 WHERE rn = 1
0
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 
LVL 74

Expert Comment

by:sdstuber
ID: 20316136
If you only have one row then NVL it


SELECT price - prevprice
  FROM (SELECT   price, NVL(LEAD(price) OVER(PARTITION BY productid ORDER BY quote_date DESC),
                            0) AS prevprice,
                 ROW_NUMBER() OVER(PARTITION BY productid ORDER BY quote_date DESC) rn
            FROM PriceHistoryTable
           WHERE productid = 1234
        ORDER BY quote_date DESC)
 WHERE rn = 1
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20316182
if you need it as part of a bigger select statement, you can create a function for it (using digital thought's code).  the select would be

Select ProductID, dbo.PriceDiff( @ProductID ) as PriceDiff  
From Product
Where ProductID in (1234, 1235, 1236)

create function dbo.PriceDiff( @productID int ) returns money
as
begin
declare @res money
SELECT
      TOP 1
      @res = CurrentPrice - (SELECT TOP 1 CurrentPrice FROM PriceHistoryTable WHERE ProductID=PHT.ProductID AND PriceDate < PHT.PriceDate ORDER BY PriceDate DESC)
FROM
      PriceHistoryTable PHT
WHERE
      PHT.ProductID=1234
ORDER BY
      PHT.PriceDate DESC
return @res
end
GO

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20316226
A function isn't necessary, MS2005 finally got analytics.

You can query the price for every productid easily


SELECT productid, price- prevprice
  FROM (SELECT   productid, price, NVL(LEAD(price) OVER(PARTITION BY productid ORDER BY quote_date DESC),
                            0) AS prevprice,
                 ROW_NUMBER() OVER(PARTITION BY productid ORDER BY quote_date DESC) rn
            FROM PriceHistoryTable)
 WHERE rn = 1
 
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20316238
ststuber: am i missing something? I don't have NVL, nor LEAD. are you sure you're on SQL 2005 and using standard built-in functins?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20316244
on second thoughts, that looks very much like Oracle.. "ROW_NUMBER()"
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20316356
I'm sorry, I combined oracle and sql server syntax.

Yes, ROW_NUMBER() IS supported in SQl Server 2005.

LEAD/LAG are not

Sorry
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20316372
you could do it with rownumber doing a sum over a case statement
price for rn = 1 an  -1 * price for rn=2

somewhat convoluted but probably more efficient than invoking a function

0
 

Author Comment

by:Dovberman
ID: 20316929
I appreciate all the responses.
I was not aware of ROW_NUMBER and the availability of the Partition function.

I will try the solutions in the morning and give feedback.

Thanks
0
 

Author Comment

by:Dovberman
ID: 20316931
For clarity, there will always be more than 1 row. The products will have a substantial price history.
0
 

Author Comment

by:Dovberman
ID: 20318924
Digital thoughts suggestion worked.

Here is the actual syntax for my application:

SELECT
      TOP 1
      (ClosePrice) -  
(SELECT TOP 1 ClosePrice FROM DayQuote
WHERE SymbolID=PHT.SymbolID AND QuoteDate < PHT.QuoteDate
ORDER BY QuoteDate DESC) AS PriceDiff
FROM
      DayQuote PHT
WHERE
      PHT.SymbolID=18487
ORDER BY
      PHT.QuoteDate
=======

My explanation was in error.

QuoteDate                         Price
2007-11-16 00:00:00.000      24.58
2007-11-15 00:00:00.000      25.54  ' I need to return (24.58 - 25.54) as PriiceDiff

Should have been I need to return (25.54 - 24.58) as PriiceDiff
Row2 value minus Row1 value

How can I reverse the syntax?

Thanks

0
 
LVL 8

Accepted Solution

by:
digital_thoughts earned 500 total points
ID: 20319981
Just reverse the order in the query:

SELECT
      TOP 1
      (SELECT TOP 1 ClosePrice FROM DayQuote
WHERE SymbolID=PHT.SymbolID AND QuoteDate < PHT.QuoteDate
ORDER BY QuoteDate DESC) - (ClosePrice) AS PriceDiff
FROM
      DayQuote PHT
WHERE
      PHT.SymbolID=18487
ORDER BY
      PHT.QuoteDate
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 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