Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

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

0
beckyng
Asked:
beckyng
  • 3
1 Solution
 
mcv22Commented:
;WITH LastOrder(Customer, O_Id) AS
(
    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
0
 
cyberkiwiCommented:
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
0
 
cyberkiwiCommented:
Interesting, my query above is by last OrderDate but the question sample result is by ID.

select O_Id,OrderDate,OrderPrice,Customer
FROM
(
select O_Id,OrderDate,OrderPrice,Customer, rn=ROW_NUMBER() over (partition by Customer order by O_Id desc)
from tbl
) SQ
where rn=1

The analytical version is normally faster than the MAX version, and is robust to break ties when the condition is not on the primary key (it seems here that it is).  If it is the unique ID, the MAX version from mcv22 can be rewritten

SELECT   OrderDate,   OrderPrice,   Customer
FROM  tbl
WHERE O_Id in (select Max(O_Id) from tbl group by Customer)
0
 
beckyngAuthor Commented:
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
0
 
cyberkiwiCommented:
select OrderDate,OrderPrice,Customer,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
0

Featured Post

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.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now