• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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

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
Dovberman
Asked:
Dovberman
  • 5
  • 4
  • 3
  • +1
1 Solution
 
imitchieCommented:
what if there is only 1 row?
0
 
digital_thoughtsCommented:
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
 
sdstuberCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sdstuberCommented:
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
 
imitchieCommented:
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
 
sdstuberCommented:
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
 
imitchieCommented:
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
 
imitchieCommented:
on second thoughts, that looks very much like Oracle.. "ROW_NUMBER()"
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
DovbermanAuthor Commented:
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
 
DovbermanAuthor Commented:
For clarity, there will always be more than 1 row. The products will have a substantial price history.
0
 
DovbermanAuthor Commented:
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
 
digital_thoughtsCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now