Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-02-27
6
Medium Priority
?
1,493 Views
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

Thanks.
0
Comment
Question by:brstores
[X]
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
6 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 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.'
0
 
LVL 93

Expert Comment

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

Author Comment

by:brstores
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 39892144
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
 

Author Comment

by:brstores
ID: 39892207
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
 

Author Comment

by:brstores
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?
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 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