Creating a script for row selection

Imagine this table :

prod_key item_key pack_key last_sale
LM001 1029 AD100 2004/12/05
LM870 1029 AD100 2005/09/20
PE789 1030 BC400 2003/07/12
PE312 1030 BC400 2004/08/07

I want to select the row which has the greater date where item_key and pack_key are equal. In other words, I want to get :

LM870 1029 AD100 2005/09/20
PE312 1030 BC400 2004/08/07

Can you help me to write the script?

    Requires Free Membership to View

    Login

Does the table have a primary key? That would make the query easier. Otherwise, I think you're after something like this:

SELECT prod_key,item_key,pack_key,last_sale
FROM (SELECT item_key,pack_key,MAX(last_sale) AS last_sale FROM tablex GROUP BY item_key,pack_key) AS MaxDateTable
WHERE tablex.item_key = MaxDateTable.item_key
AND tablex.pack_key = MaxDateTable.pack_key
AND tablex.last_sale = MaxDateTable.last_sale

This was first published in December 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top