Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Using MAX but don't want to group on everything

    I have a table that has OrderDate,Customer,OrderNo. I want to pull the most recent order date by customer, and have it show me the associated OrderNo.

    select max(orderdate),customer,orderno
    from myTable
    group by customer

    will throw an error of course, and when I add in orderno into the group, I of course don't get the data I want. I'm thinking I need a subquery in order to accomplish this but I haven't quite figured it out yet.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with CTE as
    (
        select 
            orderdate, 
            customer, 
            orderno,
            ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY OrderDate DESC) as RowNum
        from myTable
    )
    
    select * from CTE 
    where RowNum = 1
    Hope this helps.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    thanks. this got me on the right track.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •