using MAX, group by, it shoud be basic 1

[funforus]

Once again, it's been awhile since I used SQL, so I'm sorry. I did search the forum first but I haven't found exactly what I'm looking for.

Here's a sample of my data in my CASH table:

TransID EmpID Amount Date
3 7 45 4/11/2007
4 7 45 2/2/2007
5 8 12 1/5/2007
6 7 30 6/12/2007
7 3 170
8 8 10 3/6/2007


I'm trying to pull the MAX(amount) for each EmpID. I also need the TransID and Date of those associated transactions. In the case of a tie for the amount (EmpID 7 with amount 45), I need it to pull the transaction with the most recent date (some don't have dates). Here's what I would like:

TransID EmpID Amount Date
3 7 45 4/11/2007
5 8 12 1/5/2007
7 3 170

Thanks for any ideas!

 

[JackVam]

Does the following work?

SELECT c.TransID,c.EmpID,c.Amount,c.Date FROM CASH as c
WHERE c.TransID IN (SELECT TOP 1 c2.TransID FROM CASH as c2 WHERE c2.EMPID = c1.EMPID ORDER BY c2.Amount DESC)

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[nice95gle]

Not Pretty, but it's a start.

[COLOR=blue]declare[/color] @CASH  [COLOR=blue]table[/color] (TransID [COLOR=blue]int[/color], EmpID [COLOR=blue]int[/color], Amount [COLOR=blue]smallmoney[/color] ,[COLOR=blue]Date[/color] [COLOR=#FF00FF]datetime[/color])

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @CASH [COLOR=blue]values[/color](3,7,45,[COLOR=red]'04/11/2007'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @CASH [COLOR=blue]values[/color](4,7,45,[COLOR=red]'02/02/2007'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @CASH [COLOR=blue]values[/color](5,8,12,[COLOR=red]'01/05/2007'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @CASH [COLOR=blue]values[/color](6,7,30,[COLOR=red]'06/12/2007'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @CASH [COLOR=blue]values[/color](7,3,170,NULL)        
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @CASH [COLOR=blue]values[/color](8,8,10,[COLOR=red]'03/06/2007'[/color])


[COLOR=blue]select[/color] g1.transID,g2.empid,g2.Amount,[COLOR=#FF00FF]max[/color](g1.Date)[COLOR=blue]as[/color] [COLOR=blue]date[/color]
	[COLOR=blue]from[/color] @CASH g1
[COLOR=#FF00FF]right[/color] [COLOR=blue]outer[/color] [COLOR=blue]join[/color] 
([COLOR=blue]select[/color] a.EmpID,a.Amount, [COLOR=#FF00FF]Max[/color](b.date)[COLOR=blue]as[/color] [COLOR=blue]Date[/color]
	[COLOR=blue]From[/color]
		([COLOR=blue]select[/color] EmpID,[COLOR=#FF00FF]Max[/color] (Amount)[COLOR=blue]as[/color] Amount [COLOR=blue]from[/color] @CASH
			[COLOR=blue]group[/color] [COLOR=blue]by[/color]  EmpID)a
[COLOR=#FF00FF]left[/color] [COLOR=blue]Join[/color] 
		([COLOR=blue]select[/color] EmpID,Amount,[COLOR=blue]date[/color] [COLOR=blue]from[/color] @CASH
			[COLOR=blue]group[/color] [COLOR=blue]by[/color] EmpID,Amount,[COLOR=blue]date[/color])b
		[COLOR=blue]On[/color] a.empid=b.empid
		and a.Amount=b.Amount
		[COLOR=blue]group[/color] [COLOR=blue]by[/color] a.EmpID,a.Amount)g2
[COLOR=blue]on[/color] [COLOR=#FF00FF]isnull[/color](g1.Date,0)=[COLOR=#FF00FF]isnull[/color](g2.Date,0)
and g1.empid=g2.empid
and g1.Amount=g2.Amount
[COLOR=blue]group[/color] [COLOR=blue]by[/color] g1.transID,g2.empid,g2.Amount
[COLOR=blue]Order[/color] [COLOR=blue]by[/color] g2.EmpID

Well Done is better than well said
- Ben Franklin

 

[funforus]

JackVam's solution worked, though c1.empid was c.empid I think. Have a star.

I didn't have a chance to try Nice95's, but thank you both!