beckyng
asked on
Ms sql query last order by customer
I wanna to query a result for following table
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Result
2008/09/03 300 Hansen
2008/10/04 100 Nilsen
2008/08/30 2000 Jensen
Thanks
Becky
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Result
2008/09/03 300 Hansen
2008/10/04 100 Nilsen
2008/08/30 2000 Jensen
Thanks
Becky
select O_Id,OrderDate,OrderPrice, Customer
FROM
(
select O_Id,OrderDate,OrderPrice, Customer, rn=ROW_NUMBER() over (partition by Customer order by OrderDate desc)
from tbl
) SQ
where rn=1
FROM
(
select O_Id,OrderDate,OrderPrice,
from tbl
) SQ
where rn=1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all
But I have another query result wanna to get lastest record by customer & good as follows
I wanna to query a result for following table
O_Id OrderDate OrderPrice Customer Good
1 2008/11/12 1000 Hansen Chair
2 2008/10/23 1600 Nilsen Chair
3 2008/09/02 700 Hansen Table
4 2008/09/03 300 Hansen Chair
5 2008/08/30 2000 Jensen Chair
6 2008/10/04 100 Nilsen Light
Result
2008/09/03 300 Hansen Chair
2008/10/23 1600 Nilsen Chair
2008/09/02 700 Hansen Table
2008/08/30 2000 Jensen Chair
2008/10/04 100 Nilsen Light
Thanks
Becky
But I have another query result wanna to get lastest record by customer & good as follows
I wanna to query a result for following table
O_Id OrderDate OrderPrice Customer Good
1 2008/11/12 1000 Hansen Chair
2 2008/10/23 1600 Nilsen Chair
3 2008/09/02 700 Hansen Table
4 2008/09/03 300 Hansen Chair
5 2008/08/30 2000 Jensen Chair
6 2008/10/04 100 Nilsen Light
Result
2008/09/03 300 Hansen Chair
2008/10/23 1600 Nilsen Chair
2008/09/02 700 Hansen Table
2008/08/30 2000 Jensen Chair
2008/10/04 100 Nilsen Light
Thanks
Becky
select OrderDate,OrderPrice,Custo mer,Good
FROM
(
select O_Id,OrderDate,OrderPrice, Customer, rn=ROW_NUMBER() over (partition by Customer, Good order by O_Id desc)
from tbl
) SQ
where rn=1
FROM
(
select O_Id,OrderDate,OrderPrice,
from tbl
) SQ
where rn=1
(
SELECT
Customer,
MAX(O_Id)
FROM
Table
GROUP BY
Customer
)
SELECT
t.OrderDate,
t.OrderPrice,
t.Customer
FROM
LastOrder lo
JOIN
Table t
ON
lo.O_Id = t.O_Id