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

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

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

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

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

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?

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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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?

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?

from

(select part,orddate,price, row_number() over(partition by part,orddate order by part,orddate desc) rn

from yourtable

)x

where rn > 3