Query to find max value of one column based on two other columns and return add'l columns

Confusing enough title?

I have a table that has the following columns:
id, date, store, upc, price, price_type, start_date, end_date, timestamp

The only column that is unique is the id column.  There are multiple rows that have the same store and upc values.

I want to be able to return the entire row of data that has the highest timestamp based on the combination of the store and upc columns.

The following query works to find the store and upc combo that have the highest timestamp, but of course group starts throwing a fit once I try to add additional columns to return.

select store, upc, MAX(timestamp) max_timestamp
FROM table
GROUP BY store, upc

Thanks.
brstoresAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You'll need a subquery to generate the max, then the main query grabs that and specific columns, to pull this off.  Eyeballeth thy article on SQL Server GROUP BY Solutions, specifically number 5 - 'Aggregate AND values from a single row that make up the aggregate:  Subquery.'
0
Patrick MatthewsCommented:
+1 on that article Jim, nicely done
0
brstoresAuthor Commented:
The article is helpful, but I'm confused by the fact that they are joining multiple tables and all of the work that I'm doing is in one table.  I don't know what to join to what.
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Patrick MatthewsCommented:
SELECT t1.id, t1.date, t1.store, t1.upc, t1.price, t1.price_type, 
    t1.start_date, t1.end_date, t1.timestamp
FROM SomeTable t1 INNER JOIN
    (SELECT t2.store, t2.upc, MAX(t2.timestamp) AS timestamp
    FROM SomeTable t2
    GROUP BY t2.store, t2.upc) x ON t1.store = x.store AND 
        t1.upc = x.upc AND t1.timestamp = x.timestamp

Open in new window



* edited to clean up the aliases
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brstoresAuthor Commented:
Patrick, your query was giving me the following errors:
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "t2.store" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "t2.upc" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "t2.timestamp" could not be bound.

I read up on that error.  It is because SQL is unable to use t2 outide of the subquery.  I changed your query to the following:

SELECT t1.id, t1.date, t1.store, t1.upc, t1.price, t1.price_type, t1.start_date, 
    t1.end_date, t1.timestamp
FROM test t1 INNER JOIN
    (SELECT t2.store, t2.upc, MAX(t2.timestamp) AS timestamp
    FROM test t2
    GROUP BY t2.store, t2.upc) t3 ON t1.store = t3.store AND 
        t1.upc = t3.upc AND t1.timestamp = t3.timestamp
        order by upc, Store, timestamp

Open in new window

0
brstoresAuthor Commented:
What happens now if I want to actually pull a column from a second table.  For instance, if I was to want to get a department from a table that contains the upc and department.  Where would I place that additional subquery?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.