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

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
thomas-sherrouseAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
OriNetworksConnect With a Mentor Commented:
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
 
OriNetworksCommented:
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
 
FarWestCommented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
thomas-sherrouseAuthor Commented:
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
 
OriNetworksCommented:
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
 
thomas-sherrouseAuthor Commented:
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
 
thomas-sherrouseAuthor Commented:
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
 
OriNetworksCommented:
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
 
thomas-sherrouseAuthor Commented:
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
 
thomas-sherrouseAuthor Commented:
Perfect solution that would work for me.
0
 
thomas-sherrouseAuthor Commented:
Thanks - I've been racking my brain on this.
0
 
thomas-sherrouseAuthor Commented:
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
 
thomas-sherrouseAuthor Commented:
I think I figured it out, but please reply if you have a some additional info.
0
 
OriNetworksCommented:
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
 
thomas-sherrouseAuthor Commented:
Yes that is what I did. I replaced my original subquery wherever the "Transactions" Table was referenced.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.