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
1,442 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
  • 3
  • 2
6 Comments
 
LVL 65

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.'
0
 
LVL 92

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 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