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?
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
Jim HornConnect With a Mentor Microsoft 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
All Courses

From novice to tech pro — start learning today.