Post reply

Help with query - Top clause and distinct clause?

  • June 20, 2006 at 9:50 am

    #114022

    I am trying to write a query and need some help from you Guru's out there.  I am trying to find the last or latest delivery date by order number by product.

    The columns from my table have:

    Product

    DeliveryDate

    OrderNo

    The table holds 4 years of data.  I know from running a count(distinct(product)) from mytable that I am looking for 28000 individual products and their last delivery date.

    I have tried using a top 1 statement by product and ordering it by delivery date but of course, I only received the latest instance for one product. 

    Any ideas?

     

     

     

     

  • June 20, 2006 at 9:57 am

    #644863

    SELECT MAX(DeliveryDate), Product FROM YourTable GROUP BY Product

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • June 20, 2006 at 10:01 am

    #644865

    Or
     
    select t.Product, t.OrderNo, max(t.DeliveryDate) lastdeldate
    from tab t
    group by t.Product, t.OrderNo

    -requirements not entirely clear.

     

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • June 20, 2006 at 10:03 am

    #644866

    Do I hear a big D'oh!  I don't use max very often and did not even think about it!  Thanks for your help and have a great day.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply