Sql top 2 order dates for multiple parts

regbit
regbit used Ask the Experts™
on
Tables.Fields: Order.Order_Date, Order_Lines.Part_ID, Order_Lines.Price

I want a query that will give me the parts, with price, ordered each day as well as the last date, with price, each part was ordered.  ie:

Part   Ord Date  Price
1111 5/16/2011 1.00
1111 2/13/2011 1.25
2222 5/16/2011 5.00
2222 12/3/2010 4.50

and so on.

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
select part,orddate,price
from
(select part,orddate,price, row_number() over(partition by part,orddate order by part,orddate desc) rn
from yourtable
)x
where rn > 3

Commented:
select part,orddate,price
from
(select part,orddate,price, row_number() over(partition by part,orddate order by part,orddate desc) rn
from yourtable
)x
where rn < 3 --- its less than 3

Commented:
try this:

Select p.part, p.[ord date], p.price, m.[ord date] lastate, m.price lastprice
from yourtable as p inner join
(select part, [ord date], price, row_number() over(partition by part  order by [ord date] desc) as seq ) as m on m.part = p.part and p.seq=1
from yourtable

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2012

Commented:
We can only hope the author is not still using SQL Server 2000...
awking00Information Technology Specialist

Commented:
Somehow I sense that there are two tables, order and order_line, that need to be joined. What is the structure of each and can you provide some relevant sample data (i.e. for just the required fields) and the expected output?

Author

Commented:
Yes there are 2 tables that can be joined on Order ID and yes, unfortunately, this is SQL Server 2000 but we are in the process of upgrading.  The sample data in my question is how the data looks and how I would like the output.  I hope that answers the questions.  Thanks
i dont know if i am right? You wanted the records in sorted order? if that is the requirement you can do like this

select Part,    Ord_Date, Price from your table
order by Part,  Ord_Date desc , Price  

If i am wrong please let me know what exactly you want.
Commented:
For SQL 2000:

Select p.part, p.[ord date], p.price, m.[ord date] lastate, m.price lastprice
from yourtable as p inner join
(select v1.part, v1.[ord date], max(v1.price) from yourtable v1
  where v1.[ord date] =
        (select max([ord_date] from yourtable v2
              where v1.part = v2.part)
  group by v1.part, v1.[ord date]
) as m on m.part = p.part and p.seq=1
from yourtable

here is one more version for your sql 2000

SELECT * FROM (
SELECT *
     , COALESCE((
          SELECT COUNT(1)
          FROM  YourTable b
          WHERE b.Part = a.Part
          AND b.Ord_Date > a.Ord_Date
         
       ), 0)+1 RN
FROM  YourTable a
 
) derived
WHERE RN <= 2

Author

Commented:
Thank you for the replies. I got sidetracked on other issues but I will see which solution works the best in the next couple days.
awking00Information Technology Specialist

Commented:
>>The sample data in my question is how the data looks and how I would like the output. <<
If you are calling this the sample data,
Part   Ord Date  Price
1111 5/16/2011 1.00
1111 2/13/2011 1.25
2222 5/16/2011 5.00
2222 12/3/2010 4.50

Then that can't both be how it looks and how you would like the output. I suspect that is the output you want. Can you post the structure (i.e. column names and datatypes) of the order and orderline tables identifying the fields that provide the join condition?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial