Doubt in GROUP BY... - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Doubt in GROUP BY...

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-12 : 02:57:31

Hi All,

select MatchID, ClubID, Max(Distance) as 'MaxDist'
From dbo.TrendHALF_SECOND_POSITIONS
Where MatchID in (8,12)
Group By MatchID, ClubID

How to get the PlayerID who has max(distance),
If i do Group By MatchID, ClubID,PlayerID i can get the playerID,
But I need PlayerID, but PlayerID should not include in Group By clause

How can i achieve it..

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 03:00:45

Is PlayerID always having same value for a particular combination of MatchID, ClubID? If yes, you can just use any aggregate function on that field i.e MIN(PlayerID) or MAX(PlayerID). If you have more than 1 value of PlayerID for each combination of MatchID, ClubID then you need to tell whats the value of PlayerID you would be interested to get as per your business rule.

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-12 : 04:48:58

Thank You VISAKH, I got an idea and got a solution for this.

With MostPassReceive(MatchID,PassingPlayerClubID,PassingPlayerID,Passcount)
AS
(
Select MatchID,PassingPlayerClubID,PassingPlayerID,Count(PassingPlayerID) As Passcount
From TrendEvents_Distribution
Group By MatchID,PassingPlayerClubID,PassingPlayerID
)

Select P.MatchID,P.PassingPlayerClubID,dbo.fn_ResolvePlayerID(P.PassingPlayerID) As Player,
PassCount
From MostPassReceive P Where
0 = (Select Count(Distinct Passcount) From MostPassReceive C
Where C.PassCount > P.PassCount And P.MatchID=C.MatchID
And P.PassingPlayerClubID=C.PassingPlayerClubID
)
order By P.MatchID

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-12 : 05:44:27

hello Visakh, My query returns the Same Passcount for a two different players for playerclubid.
Can u help me how to eliminate dupliates.
I want to eliminate the record which i marked in box.
The below is the result
0 88 10 Celestine 31
0 89 20 Denny LANDZAAT 35
-----------------------------------------------
0 88 5 Scott PARKER 31
-----------------------------------------------
1 88 7 Titus BRAMBLE 66
1 90 30 Gavin MCCANN 48
2 91 46 Michael BROWN 28
2 88 3 Belozoglu EMRE 39
3 92 65 Lee BOWYER 37
3 88 3 Belozoglu EMRE 40
4 93 78 Xabi ALONSO 58
4 88 10 Celestine BABAYARO 40
5 88 3 Belozoglu EMRE 34
5 94 93 Phil NEVILLE 42
6 95 106 Paul SCHOLES 83
6 88 3 Belozoglu EMRE 40
7 88 115 Scott PARKER 40
7 96 124 Kevin NOLAN 29
7 88 112 Belozoglu EMRE 40
8 88 5 Scott PARKER 43
8 97 135 George BOATENG 38
9 88 5 Scott PARKER 60
9 9 152 Andy REID 38
10 88 5 Scott PARKER 35
10 98 170 Mikele LEIGERTWOOD 35
11 88 3 Belozoglu EMRE 39
11 99 179 Joey BARTON 39
12 1 190 Francesc FABREGAS 75
12 88 111 Nolberto SOLANO 38
---------------------------------------------
12 88 8 Nicky BUTT 38
---------------------------------------------
13 100 212 Sean DAVIS 36
13 88 111 Nolberto SOLANO 62
14 88 202 Emre BELOZOGLU 56
14 101 224 John OSTER 41
16 88 8 Nicky BUTT 39
16 102 255 John TERRY 64
17 88 10 Celestine BABAYARO 41
17 103 263 Gavin MAHON 27
18 88 111 Nolberto SOLANO 43
18 104 282 Tom HUDDLESTONE 50
19 96 129 El-Hadji DIOUF 41
19 88 8 Nicky BUTT 38
20 94 93 Phil NEVILLE 40
20 88 8 Nicky BUTT 57
21 88 202 Emre BELOZOGLU 34
21 95 106 Paul SCHOLES 61
22 88 188 Kieron DYER 56
22 104 282 Tom HUDDLESTONE 47
23 92 303 Nigel QUASHIE 35
23 88 111 Nolberto SOLANO 57
24 88 188 Kieron DYER 39
24 90 29 Gareth BARRY 41
25 88 8 Nicky BUTT 43
25 91 46 Michael BROWN 46
26 93 75 Steven GERRARD 48
26 88 13 Steven TAYLOR 48
27 89 20 Denny LANDZAAT 35
27 88 5 Scott PARKER 24
28 88 111 Nolberto SOLANO 58
28 97 135 George BOATENG 37
29 9 149 Luke YOUNG 30
29 88 5 Scott PARKER 61
30 99 345 Michael JOHNSON 37
30 88 1 Stephen CARR 64
31 88 1 Stephen CARR 42
31 1 190 Francesc FABREGAS 57
32 102 353 John MIKEL 52
32 88 3 Belozoglu EMRE 46
33 88 188 Kieron DYER 50
33 101 224 John OSTER 29
34 88 111 Nolberto SOLANO 53
34 5 233 Tugay KERIMOGLU 33
35 88 8 Nicky BUTT 59
35 103 270 Adrian MARIAPPA 29
36 5 233 Tugay KERIMOGLU 70
36 88 8 Nicky BUTT 55
37 9 154 Matt HOLLAND 45
37 95 492 John O'SHEA 65
38 102 351 Wayne BRIDGE 53
38 91 278 Danny MURPHY 46
39 1 190 Francesc FABREGAS 69
39 92 232 Lucas NEILL 52
40 114 279 Hossam GHALY 26
40 113 518 Fabrice MUAMBA 29
41 115 533 Velice SUMULIKOSKI 62
41 116 249 Frank LAMPARD 52
43 5 237 David BENTLEY 38
43 94 89 Mikel ARTETA 29
44 95 292 Ryan GIGGS 39
44 1 190 Francesc 61
45 94 93 Phil NEVILLE 54
45 96 126 Gary SPEED 30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 05:58:32

May be this:-

;
With MostPassReceive(MatchID,PassingPlayerClubID,PassingPlayerID,Passcount)
AS
(
Select MatchID,PassingPlayerClubID,PassingPlayerID,Count(PassingPlayerID) As Passcount
From TrendEvents_Distribution
Group By MatchID,PassingPlayerClubID,PassingPlayerID
)
SELECT * FROM
(
Select ROW_NUMBER() OVER(PARTITION BY P.MatchID,P.PassingPlayerClubID ORDER BY P.MatchID) AS RowNo,
P.MatchID,P.PassingPlayerClubID,dbo.fn_ResolvePlayerID(P.PassingPlayerID) As Player,
PassCount
From MostPassReceive P Where 
0 = (Select Count(Distinct Passcount) From MostPassReceive C 
Where C.PassCount > P.PassCount And P.MatchID=C.MatchID 
And P.PassingPlayerClubID=C.PassingPlayerClubID
)
)tmp
WHERE tmp.RowNo =1
order By tmp.MatchID

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-12 : 06:03:21

Thanks a lot....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources