Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-11-19
14
Medium Priority
?
201 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
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.

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

609 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