Q

Creating a script for row selection

Development expert Greg Low offers a script to select a row with the greater date, where item_key and pack_key are equal.

This Content Component encountered an error
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?

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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close