Solved

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

Posted on 2007-11-19
14
196 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

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