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,432 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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