Solved

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

Posted on 2007-11-19
14
193 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
  • 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

792 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