Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NervousRex
Starting Member
9 Posts |
 Posted - 2010-06-08 : 15:21:40
|
| I have a table that stores an employees score for a project over the last 6 months, each client can have multiple projects, and each project has multiple employees working on it.My table columns with example data...[Row_ID] 1,2,3,4...[Client_ID] A,A,B,B[Project_ID] A1, A1, B1, B1[Employee_ID] 20,21,20,21[Score] .80, .90, .25, .07I would like my query to return the Max score for each Project...2 , A , A1 , 21 , .903 , B , B1 , 20 , .25I'm looking to join these results in with another query that will have the [Client_ID] and [Project_ID] to join on. |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-06-08 : 15:28:05
|
| [code]select Row_ID,Client_ID,Project_ID,Employee_ID,Score from (select row_number() over(partition by Client_ID,Project_ID order by Score desc) as seq, * from MyTable) twhere t.seq = 1[/code] |
 |
|
|
NervousRex
Starting Member
9 Posts |
Posted - 2010-06-08 : 16:04:18
|
| I never even knew about the 'Over' function or 'Partition', and you bust them both out in the same query.And it worked very nicely!Thanks |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-06-08 : 16:20:54
|
| Np. You're welcome. |
|
|
|
|
|
|
|
|
Max Value Grouping