Link to home
Start Free TrialLog in
Avatar of regbit
regbit

asked on

Sql top 2 order dates for multiple parts

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
Avatar of sventhan
sventhan
Flag of United States of America image

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

Avatar of Anthony Perkins
We can only hope the author is not still using SQL Server 2000...
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?
Avatar of regbit
regbit

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of regbit

ASKER

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.
>>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?