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

Posted on 2014-02-27
Last Modified: 2014-02-27
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

Question by:brstores
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39892024
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.'
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39892100
+1 on that article Jim, nicely done

Author Comment

ID: 39892117
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.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 92

Accepted Solution

Patrick Matthews earned 250 total points
ID: 39892144
SELECT,,, t1.upc, t1.price, t1.price_type, 
    t1.start_date, t1.end_date, t1.timestamp
    (SELECT, t2.upc, MAX(t2.timestamp) AS timestamp
    FROM SomeTable t2
    GROUP BY, t2.upc) x ON = AND 
        t1.upc = x.upc AND t1.timestamp = x.timestamp

Open in new window

* edited to clean up the aliases

Author Comment

ID: 39892207
Patrick, your query was giving me the following errors:
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "" 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.upc, t1.price, t1.price_type, t1.start_date, 
    t1.end_date, t1.timestamp
    (SELECT, t2.upc, MAX(t2.timestamp) AS timestamp
    FROM test t2
    GROUP BY, t2.upc) t3 ON = AND 
        t1.upc = t3.upc AND t1.timestamp = t3.timestamp
        order by upc, Store, timestamp

Open in new window


Author Comment

ID: 39892867
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?

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor ( Top Charts is a view in which you can set seve…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question