?
Solved

SQL Query to Return a Data Set by Grouping Columns and Determining Max of One Value and Return another Column - SubQuery

Posted on 2015-01-21
15
Medium Priority
?
123 Views
Last Modified: 2015-01-22
I've got a data set where I have Employee ID's with varying Assignment ID's that have monthly pay amounts. I'd like to determine which of the Employee's Assignment ID's had the most Pay Amount per month/year. I've attached an excel example of the data set along with what my desired data set output would be.

From my Data Set I basically want to Determine the Max Amount Value Group By [EmployeeID], [Month], [Year] and then Return whichever AssignmentID is corresponding to that Max Value amount

Something like this:

SELECT Transactions.[EmployeeID], Transactions.[AssignmentID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year];

Open in new window

MaxID-BasedonValue-Example.xlsx
0
Comment
Question by:thomas-sherrouse
[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
  • 9
  • 5
15 Comments
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40562601
That is a good start but you can use that as a subquery and join the table again.

Select t1.*,t2.AssignmentID
SELECT Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year]) t1
left join transactions t2 on t1.EmployeeID=t2.EmployeeID and t1.[Month]=t2.[Month] and  t1.[Year]=t2.[Year] and t1.MaxOfPayAmount = t2.PayAmount

Open in new window

0
 
LVL 12

Expert Comment

by:FarWest
ID: 40562629
try to use this method

SELECT *
FROM t1 WHERE (id,rev) IN
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)
//
SELECT * from Transactions t1 Where (t1.[EmployeeID], t1.[AssignmentID], t1.[Month], t1.[Year])
IN (
SELECT Transactions.[EmployeeID], Transactions.[AssignmentID], Transactions.[Month], Transactions.[Year],Max(Transactions.[PayAmount])
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year])
0
 

Author Comment

by:thomas-sherrouse
ID: 40562689
OriNetworks - It looks like you're missing a "(" in there.

Here's the Error I get when I run it:

"Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'."
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 17

Expert Comment

by:OriNetworks
ID: 40562706
Then the correct syntax should be
Select t1.*,t2.AssignmentID
 FROM
(
SELECT Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year]
) t1
left join transactions t2 on t1.EmployeeID=t2.EmployeeID and t1.[Month]=t2.[Month] and  t1.[Year]=t2.[Year] 

Open in new window

0
 

Author Comment

by:thomas-sherrouse
ID: 40562717
It gives me the correct "MaxOfPayAmount" but doesn't identify the correct AssignmentID. It just lists all of the them

EmployeeID	Month	Year	MaxOfPayAmount	AssignmentID
1	1	2014	1000	2222
1	1	2014	1000	3333
1	1	2014	1000	4444
1	2	2014	575	2222
1	2	2014	575	3333
1	2	2014	575	4444
2	1	2014	1250	1111
2	1	2014	1250	5555
2	1	2014	1250	6666
2	2	2014	500	1111
2	2	2014	500	5555
2	2	2014	500	6666
3	1	2014	1250	7777
3	1	2014	1250	9999
3	1	2014	1250	8888
3	2	2014	5000	7777
3	2	2014	5000	9999
3	2	2014	5000	8888

Open in new window

0
 

Author Comment

by:thomas-sherrouse
ID: 40562730
K It looks like the "MaxofPayAmount" Join was left off.

Is this correct?
Select t1.*,t2.AssignmentID
 FROM
(
SELECT Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year]
) t1
left join transactions t2 on t1.EmployeeID=t2.EmployeeID and t1.[Month]=t2.[Month] and  t1.[Year]=t2.[Year]  and t1.MaxOfPayAmount = t2.PayAmount

Open in new window

0
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40562745
Oh my, I guess when I copied it for correction it did not come over. My Apologies but yes that should be it although I admit I did not look at your sample attachment
0
 

Author Comment

by:thomas-sherrouse
ID: 40562766
Well kudos to you it looks like everything is working! My only concern is the join on the values. That seems like something that could cause an error or two because this value isn't a primary key anywhere and could be duplicated.
0
 
LVL 17

Accepted Solution

by:
OriNetworks earned 2000 total points
ID: 40562783
Yes, the values could be duplicated but in the event that the same employee for the same year and month has 2 payment that match the same max amount, how would you handle that anyway? Without knowing more about the application it would be difficult to make that guess. If maybe you want only that latest AssignmentID in case of duplicates you could use:

Select t1.*,MAX(t2.AssignmentID) AS AssignmentID
 FROM
(
SELECT Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year]
) t1
left join transactions t2 on t1.EmployeeID=t2.EmployeeID and t1.[Month]=t2.[Month] and  t1.[Year]=t2.[Year]  and t1.MaxOfPayAmount = t2.PayAmount
GROUP BY t1.EmployeeID, t1.Month, t1.Year, t1.MaxOfPayAmount

Open in new window

0
 

Author Comment

by:thomas-sherrouse
ID: 40562813
Perfect solution that would work for me.
0
 

Author Closing Comment

by:thomas-sherrouse
ID: 40562815
Thanks - I've been racking my brain on this.
0
 

Author Comment

by:thomas-sherrouse
ID: 40562874
OriNetworks - I'm having an issue incorporating this into the actual query. My original query that produced the data set consisted of joins to multiple tables so I doesn't seem I can declare the query as "t2" as if it were a standalone table. does that make sense?
0
 

Author Comment

by:thomas-sherrouse
ID: 40562876
I think I figured it out, but please reply if you have a some additional info.
0
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40564975
If you are trying to attach this as a subquery to an additional larger query you may have to wrap it up in another subquery and join it again.
select * from thisIsMainQueryTable inner join
(this is the query in my answer) t3 on ....

Otherwise if your existing query already uses t2 as an alias for a different table you can just change all the references to t2 in the answer to something else.
0
 

Author Comment

by:thomas-sherrouse
ID: 40565466
Yes that is what I did. I replaced my original subquery wherever the "Transactions" Table was referenced.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

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. …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

764 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