Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: