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.

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 last published in December 2005

Dig Deeper on SQL-Transact SQL (T-SQL)

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close