We're a community of 1.1M IT Pros here for help, advice, solutions, professional growth and fun. Join us!
1,080,668 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Start New Discussion Reply to this Discussion
Ad: Discover Popular Web Based CRM Apps
5 Months Ago
0

Selecting second greatest value

Hi all,

This is a follow on from my last post.

Here is my current query:

SELECT * FROM (
    SELECT * FROM (
        SELECT TeamID, PlayerID, COUNT(*) AS Total
        FROM Scored
        WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
        GROUP BY PlayerID
    ) T
    GROUP BY TeamID, Total DESC
) T
GROUP BY TeamID, Total DESC

Giving me as the result:

TeamID PlayerID Total
A   James   34
A   Peter   28
A   Amy     23
A   Carl    3
A   Sam     1
B   David   8
B   Steven  7
B   Claire  4
B   Andrew  3
C   John    29
C   Robert  28
C   Angela  18
C   Sarah   14
C   Tom     12
C   Craig   11
C   Zach    10
C   Gregory 8
C   Larry   4
C   Curly   2
C   Moe     1

What I'm after is to select only the second highest scorer from each TeamID, and if a team were to have only one player then that player is not selected.

Now what I also have is:

SELECT TeamID, PlayerID, MAX(Total) AS Total FROM (
    SELECT TeamID, PlayerID, COUNT(*) AS Total
    FROM Scored
    WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
    GROUP BY PlayerID
    ORDER BY Total DESC
) T
GROUP BY TeamID

which gives me a selection of only the top scorer from each team.

So I was wondering if there was a way to compare the two results and remove the duplicate PlayerIDs so that I can then just select the MAX(total) once again from what is left behind?

NB: I am using Navicat for MySQL and am thus unable to create temporary tables.

Any help would be very much appreciated.

Thanks.

2
Contributors
10
Replies
1 Day
Discussion Span
5 Months Ago
Last Updated
11
Views
Question
Answered
Tinnin
Junior Poster
160 posts since Jul 2012
Reputation Points: 8
Solved Threads: 1
Skill Endorsements: 2
5 Months Ago
0

Provide an sql script with your table(s) and sample data.

pritaeas
Posting Prodigy
Moderator
9,533 posts since Jul 2006
Reputation Points: 1,194
Solved Threads: 1,494
Skill Endorsements: 98
5 Months Ago
0

I understand that it would be very useful to provide that information but unfortunately the tables I am working from contain a massive amount of data, most of which is highly sensitive information.
In the previous post I've changed what the tables are in fact about. They still address the same issue however.

Sorry for being awkward.

Tinnin
Junior Poster
160 posts since Jul 2012
Reputation Points: 8
Solved Threads: 1
Skill Endorsements: 2
5 Months Ago
0

All is obiously not necessary, just enough table columns and some data (as shown above) to be able to reproduce the issue.

pritaeas
Posting Prodigy
Moderator
9,533 posts since Jul 2006
Reputation Points: 1,194
Solved Threads: 1,494
Skill Endorsements: 98
5 Months Ago
0

This is a sample of the data from the table. I've only included the columns I'm using.
So everytime a player scores, a new row is created for their ID.

I'm trying to find the second top scorer for each team for the month of December in 2012.

Cheers.

TeamID  PlayerID    GoalDate
A   1217    07/12/2012
B   2301    07/12/2012
C   2242    07/12/2012
D   3060    07/12/2012
E   2907    07/12/2012
F   3099    07/12/2012
G   2525    13/12/2012
H   3083    07/12/2012
I   1020    07/12/2012
J   2362    07/12/2012
K   2513    07/12/2012
H   3081    07/12/2012
K   2883    07/12/2012
B   2523    07/12/2012
L   2913    07/12/2012
F   3099    07/12/2012
E   3114    07/12/2012
M   2570    07/12/2012
N   1858    07/12/2012
G   2525    07/12/2012
E   512     07/12/2012
N   1858    07/12/2012
G   1696    07/12/2012
D   3057    07/12/2012
I   2910    07/12/2012
H   3085    07/12/2012
O   2350    07/12/2012
H   3081    07/12/2012
G   2807    07/12/2012
F   3099    07/12/2012
F   3096    07/12/2012
G   1696    07/12/2012
O   3026    07/12/2012
D   3058    07/12/2012
O   3129    07/12/2012
F   3099    07/12/2012
D   3057    07/12/2012
C   2707    07/12/2012
L   2879    07/12/2012
B   2523    07/12/2012
F   3096    07/12/2012
H   3085    07/12/2012
F   3095    07/12/2012
C   2707    07/12/2012
N   1858    07/12/2012
F   3099    07/12/2012
A   1217    07/12/2012
H   3127    07/12/2012
F   3095    07/12/2012
F   3095    07/12/2012
K   2883    08/12/2012
G   2565    08/12/2012
H   3081    08/12/2012
I   2910    08/12/2012
G   2525    08/12/2012
P   1541    08/12/2012
K   1532    08/12/2012
K   1532    08/12/2012
I   2909    08/12/2012
M   2570    08/12/2012
O   2713    08/12/2012
F   3107    08/12/2012
I   2910    08/12/2012
Q   3075    08/12/2012
O   2771    08/12/2012
D   3059    08/12/2012
K   1532    08/12/2012
G   2961    08/12/2012
O   2713    08/12/2012
E   2179    07/12/2012
K   1532    08/12/2012
L   2879    08/12/2012
K   2883    08/12/2012
K   1532    08/12/2012
R   2015    08/12/2012
O   2350    08/12/2012
E   2934    08/12/2012
K   1532    08/12/2012
E   2934    08/12/2012
E   2934    08/12/2012
E   3074    08/12/2012
E   3070    08/12/2012
B   2303    08/12/2012
A   1217    08/12/2012
K   1532    08/12/2012
K   2883    08/12/2012
R   2015    08/12/2012
E   3120    08/12/2012
E   2934    08/12/2012
H   3081    05/12/2012
F   3095    08/12/2012
P   1541    08/12/2012
P   1541    08/12/2012
K   2883    08/12/2012
S   2943    08/12/2012
D   3062    07/12/2012
G   2565    08/12/2012
O   2350    08/12/2012
K   2513    10/12/2012
K   2816    08/12/2012
K   2513    07/12/2012
K   2513    08/12/2012
K   2883    10/12/2012
A   1217    10/12/2012
K   2816    10/12/2012
A   1217    30/11/2012
D   3058    10/12/2012
A   1217    04/12/2012
O   3129    08/12/2012
O   3129    08/12/2012
O   3129    08/12/2012
K   1532    10/12/2012
O   3129    08/12/2012
K   2816    10/12/2012
K   1532    10/12/2012
K   2816    10/12/2012
E   3070    07/12/2012
S   3030    06/12/2012
S   3030    06/12/2012
P   1541    10/12/2012
S   3030    10/12/2012
A   1217    27/11/2012
K   1532    10/12/2012
Q   2911    10/12/2012
T   3139    10/12/2012
K   1532    10/12/2012
S   2943    10/12/2012
P   1541    10/12/2012
K   2883    10/12/2012
K   2816    10/12/2012
P   1541    10/12/2012
H   3082    10/12/2012
A   1217    10/12/2012
F   3095    10/12/2012
T   3139    10/12/2012
H   3081    05/12/2012
F   3096    10/12/2012
D   3057    10/12/2012
T   3142    10/12/2012
K   2883    10/12/2012
F   3095    10/12/2012
D   3056    10/12/2012
H   3082    10/12/2012
D   3060    10/12/2012
K   1532    10/12/2012
P   1541    10/12/2012
K   2816    10/12/2012
U   3014    10/12/2012
O   2140    10/12/2012
D   3057    10/12/2012
E   814     10/12/2012
D   3058    10/12/2012
D   3059    10/12/2012
A   1217    10/12/2012
T   3142    10/12/2012
O   2771    10/12/2012
B   2301    10/12/2012
K   2816    10/12/2012
G   2565    10/12/2012
V   3001    10/12/2012
D   3056    10/12/2012
A   1217    10/12/2012
K   2883    10/12/2012
E   2179    10/12/2012
M   2817    10/12/2012
W   2968    10/12/2012
I   2910    10/12/2012
G   2886    09/12/2012
S   3138    10/12/2012
O   2350    10/12/2012
H   3085    05/12/2012
E   52      10/12/2012
G   2565    10/12/2012
G   2525    11/12/2012
E   2934    10/12/2012
G   2525    10/12/2012
Q   2878    10/12/2012
G   3110    10/12/2012
E   2934    10/12/2012
L   2913    10/12/2012
D   3056    10/12/2012
Q   3015    10/12/2012
C   2938    10/12/2012
G   2886    09/12/2012
E   53      10/12/2012
E   3074    10/12/2012
Q   2878    10/12/2012
V   2008    10/12/2012
E   52      10/12/2012
G   3110    10/12/2012
V   3001    10/12/2012
H   3083    10/12/2012
U   3014    10/12/2012
O   2771    10/12/2012
A   1217    10/12/2012
E   53      10/12/2012
D   3057    10/12/2012
D   3058    10/12/2012
U   3014    10/12/2012
G   2565    10/12/2012
E   512     10/12/2012
D   3056    10/12/2012
Q   2878    10/12/2012
M   2817    10/12/2012
W   2968    10/12/2012
E   3070    10/12/2012
I   2910    10/12/2012
D   3058    10/12/2012
D   3057    10/12/2012
E   3120    10/12/2012
E   52      10/12/2012
Q   2878    10/12/2012
C   2938    10/12/2012
F   3099    11/12/2012
F   3099    11/12/2012
K   2883    11/12/2012
K   2513    11/12/2012
K   1532    11/12/2012
V   1900    11/12/2012
K   2816    11/12/2012
T   3138    11/12/2012
K   1532    11/12/2012
Q   3015    11/12/2012
P   1541    11/12/2012
P   3028    11/12/2012
D   3056    11/12/2012
O   2680    11/12/2012
G   2886    11/12/2012
Q   3015    11/12/2012
G   2565    11/12/2012
P   1541    11/12/2012
D   3060    11/12/2012
Q   3075    11/12/2012
T   3149    11/12/2012
D   3057    11/12/2012
Q   2878    11/12/2012
V   1900    11/12/2012
G   2525    11/12/2012
K   1532    11/12/2012
D   3056    11/12/2012
K   2513    11/12/2012
K   2883    11/12/2012
P   1541    11/12/2012
G   2961    11/12/2012
K   1532    11/12/2012
H   3082    11/12/2012
H   3083    11/12/2012
K   2883    11/12/2012
E   53      11/12/2012
G   2807    11/12/2012
D   3057    11/12/2012
T   3149    11/12/2012
E   52      11/12/2012
G   2807    11/12/2012
E   54      11/12/2012
F   3095    11/12/2012
I   2910    11/12/2012
B   3132    11/12/2012
O   2680    11/12/2012
D   3057    11/12/2012
E   3136    11/12/2012
H   3085    11/12/2012
H   3085    11/12/2012
K   2883    11/12/2012
E   54      11/12/2012
H   3081    11/12/2012
I   2698    11/12/2012
K   1532    11/12/2012
Q   2878    11/12/2012
T   3142    11/12/2012
D   3056    11/12/2012
E   52      11/12/2012
I   1020    11/12/2012
L   2913    11/12/2012
E   3120    11/12/2012
G   3146    11/12/2012
I   2909    11/12/2012
B   2301    11/12/2012
O   2680    11/12/2012
Q   2878    11/12/2012
G   2525    11/12/2012
G   2565    11/12/2012
I   2909    11/12/2012
E   52      11/12/2012
I   2909    11/12/2012
E   3078    11/12/2012
H   3082    11/12/2012
D   3056    11/12/2012
H   3085    11/12/2012
V   1900    11/12/2012
E   3136    11/12/2012
V   2008    11/12/2012
F   3099    11/12/2012
C   2941    11/12/2012
H   3083    11/12/2012
O   2713    11/12/2012
D   3056    11/12/2012
O   1927    11/12/2012
E   512     11/12/2012
Q   2878    11/12/2012
H   3085    11/12/2012
X   3131    11/12/2012
M   2817    11/12/2012
D   3060    11/12/2012
F   3099    11/12/2012
G   2807    11/12/2012
E   2934    11/12/2012
D   3057    11/12/2012
D   3060    11/12/2012
M   2817    11/12/2012
X   3131    11/12/2012
V   1900    11/12/2012
D   3059    11/12/2012
H   3083    11/12/2012
E   512     11/12/2012
E   53      11/12/2012
E   2934    11/12/2012
L   2913    11/12/2012
E   2934    11/12/2012
V   1900    11/12/2012
E   52      11/12/2012
X   3131    11/12/2012
P   1541    12/12/2012
K   2883    11/12/2012
K   2816    11/12/2012
K   2816    11/12/2012
P   1541    12/12/2012
K   2883    12/12/2012
P   3028    12/12/2012
K   2513    12/12/2012
O   2680    12/12/2012
Y   3037    12/12/2012
V   1900    12/12/2012
F   3096    12/12/2012
P   3028    12/12/2012
O   2771    12/12/2012
T   3139    12/12/2012
O   2140    12/12/2012
V   3001    12/12/2012
P   1541    12/12/2012
V   1900    12/12/2012
O   2350    12/12/2012
N   1858    12/12/2012
E   53      12/12/2012
T   3139    12/12/2012
K   2816    12/12/2012
A   1217    11/12/2012
D   3057    12/12/2012
P   1541    12/12/2012
K   2816    12/12/2012
L   2913    12/12/2012
G   3110    12/12/2012
H   3082    12/12/2012
T   3142    12/12/2012
D   3057    12/12/2012
I   2698    12/12/2012
G   2566    12/12/2012
G   2565    12/12/2012
N   1858    12/12/2012
T   3142    11/12/2012
E   52      12/12/2012
I   1020    12/12/2012
G   2565    12/12/2012
V   2008    12/12/2012
K   2816    12/12/2012
T   3139    12/12/2012
G   2886    12/12/2012
O   2350    12/12/2012
G   3110    12/12/2012
E   54      12/12/2012
D   3057    12/12/2012
H   3083    12/12/2012
S   2943    12/12/2012
I   1020    12/12/2012
D   3056    12/12/2012
E   814     12/12/2012
H   3081    12/12/2012
G   2525    12/12/2012
N   1858    12/12/2012
K   2816    12/12/2012
E   53      12/12/2012
V   3001    12/12/2012
D   3058    12/12/2012
C   2941    12/12/2012
E   52      12/12/2012
V   3001    12/12/2012
Q   3075    12/12/2012
E   3070    12/12/2012
F   3095    12/12/2012
G   2961    12/12/2012
F   3099    12/12/2012
D   3057    12/12/2012
S   2943    25/11/2012
S   2943    12/12/2012
Y   3037    12/12/2012
D   3058    12/12/2012
E   2179    12/12/2012
Q   2878    12/12/2012
S   3030    27/11/2012
E   52      12/12/2012
M   2817    12/12/2012
F   3099    19/12/2012
E   2179    12/12/2012
O   2713    12/12/2012
G   3110    12/12/2012
E   2934    12/12/2012
I   2910    12/12/2012
Q   2878    12/12/2012
I   2909    12/12/2012
L   2913    12/12/2012
N   1858    12/12/2012
I   2910    12/12/2012
V   1900    12/12/2012
G   3110    12/12/2012
F   3096    12/12/2012
Q   3075    12/12/2012
F   3099    12/12/2012
G   3146    12/12/2012
O   2350    12/12/2012
D   3058    12/12/2012
V   1900    12/12/2012
E   814     12/12/2012
M   2570    12/12/2012
E   52      12/12/2012
Q   3075    12/12/2012
E   3070    12/12/2012
C   2941    12/12/2012
E   53      12/12/2012
G   2807    12/12/2012
V   2008    12/12/2012
N   1858    12/12/2012
Q   2878    12/12/2012
B   2523    12/12/2012
O   2771    12/12/2012
E   52      12/12/2012
E   3116    12/12/2012
V   1900    12/12/2012
B   2523    12/12/2012
E   2778    12/12/2012
O   2771    12/12/2012
C   2941    12/12/2012
S   3030    13/12/2012
A   1217    12/12/2012
P   1541    13/12/2012
P   1541    13/12/2012
K   2883    13/12/2012
A   1217    13/12/2012
P   1541    13/12/2012
T   3139    13/12/2012
P   1541    13/12/2012
K   2816    13/12/2012
T   3152    13/12/2012
V   1900    13/12/2012
P   1541    13/12/2012
V   1900    13/12/2012
T   3152    13/12/2012
D   3057    13/12/2012
T   3152    13/12/2012
Z   2939    13/12/2012
E   52      13/12/2012
D   3057    13/12/2012
E   512     13/12/2012
D   3056    13/12/2012
E   53      13/12/2012
E   512     13/12/2012
G   3146    13/12/2012
L   2913    13/12/2012
K   2883    13/12/2012
O   1927    13/12/2012
G   1696    13/12/2012
G   2807    13/12/2012
X   3130    13/12/2012
V   1900    13/12/2012
E   3150    13/12/2012
F   3099    13/12/2012
G   2525    13/12/2012
M   2817    13/12/2012
F   3096    13/12/2012
G   2565    13/12/2012
O   2680    13/12/2012
M   1513    13/12/2012
N   1858    13/12/2012
V   2008    13/12/2012
B   2523    13/12/2012
H   3081    13/12/2012
H   3085    13/12/2012
T   3152    13/12/2012
N   1858    13/12/2012
E   53      13/12/2012
I   2909    13/12/2012
E   3153    13/12/2012
M   2570    13/12/2012
T   3152    13/12/2012
L   2913    13/12/2012
G   2886    13/12/2012
M   2817    13/12/2012
D   3060    13/12/2012
V   3001    13/12/2012
T   3152    13/12/2012
J   2202    13/12/2012
V   1900    13/12/2012
L   2913    13/12/2012
D   3059    13/12/2012
E   512     13/12/2012
W   2968    13/12/2012
G   2961    13/12/2012
H   3085    13/12/2012
J   2202    13/12/2012
E   3153    13/12/2012
O   2680    13/12/2012
N   1858    13/12/2012
F   3095    13/12/2012
H   3083    13/12/2012
Q   2878    13/12/2012
L   2913    13/12/2012
D   3056    13/12/2012
B   2523    13/12/2012
E   53      13/12/2012
Q   2878    13/12/2012
G   2807    13/12/2012
E   2934    13/12/2012
G   2525    13/12/2012
D   3060    13/12/2012
G   3146    13/12/2012
H   3085    13/12/2012
O   1927    13/12/2012
D   3058    13/12/2012
E   52      13/12/2012
L   3144    13/12/2012
F   3099    13/12/2012
H   3085    13/12/2012
V   1900    13/12/2012
E   53      13/12/2012
E   52      13/12/2012
K   2883    14/12/2012
T   3139    14/12/2012
D   3056    14/12/2012
V   2008    14/12/2012
D   3059    14/12/2012
K   2513    14/12/2012
T   3152    14/12/2012
D   3056    14/12/2012
D   3058    14/12/2012
E   814     18/12/2012
T   3152    13/12/2012
V   3001    14/12/2012
D   3057    14/12/2012
V   2094    14/12/2012
V   2008    14/12/2012
K   2883    14/12/2012
H   3085    14/12/2012
O   2350    14/12/2012
H   3081    14/12/2012
V   2008    14/12/2012
K   2883    14/12/2012
F   3096    14/12/2012
F   3099    14/12/2012
O   2680    14/12/2012
H   3085    14/12/2012
E   52      14/12/2012
G   2807    14/12/2012
B   2523    14/12/2012
O   2350    14/12/2012
C   2707    14/12/2012
X   3154    14/12/2012
G   3146    14/12/2012
I   2910    14/12/2012
N   1858    14/12/2012
D   3058    14/12/2012
D   3056    14/12/2012
F   3099    14/12/2012
G   2807    14/12/2012
G   2565    14/12/2012
D   3060    14/12/2012
E   512     14/12/2012
O   2350    14/12/2012
F   3099    14/12/2012
E   512     14/12/2012
E   2934    14/12/2012
E   52      14/12/2012
K   2883    14/12/2012
O   2350    14/12/2012
G   3146    14/12/2012
H   3082    14/12/2012
G   2961    14/12/2012
K   2883    14/12/2012
D   3056    14/12/2012
T   3152    14/12/2012
D   3062    14/12/2012
G   2807    14/12/2012
D   3062    14/12/2012
D   3058    14/12/2012
F   3099    14/12/2012
F   3095    14/12/2012
N   1858    14/12/2012
G   2565    14/12/2012
E   3153    14/12/2012
E   2934    14/12/2012
D   3057    14/12/2012
N   1858    14/12/2012
E   512     14/12/2012
D   3057    14/12/2012
E   3150    14/12/2012
D   3060    14/12/2012
E   3153    14/12/2012
D   3057    14/12/2012
E   2778    14/12/2012
N   1858    14/12/2012
L   2913    14/12/2012
E   3153    14/12/2012
X   3131    14/12/2012
K   2883    15/12/2012
G   2807    15/12/2012
G   2807    15/12/2012
K   2816    15/12/2012
G   2565    15/12/2012
Q   2878    15/12/2012
D   3058    15/12/2012
O   2350    15/12/2012
U   2985    15/12/2012
D   3058    15/12/2012
O   2680    15/12/2012
K   1532    15/12/2012
Q   2878    15/12/2012
O   2140    15/12/2012
D   3062    15/12/2012
D   3059    15/12/2012
H   3081    15/12/2012
O   3066    15/12/2012
A   1217    15/12/2012
Q   2878    15/12/2012
K   2816    15/12/2012
H   3082    15/12/2012
E   3078    15/12/2012
L   2913    15/12/2012
O   2771    15/12/2012
E   2179    15/12/2012
T   3138    15/12/2012
D   3058    15/12/2012
H   3085    15/12/2012
O   2350    15/12/2012
C   2707    15/12/2012
D   3057    15/12/2012
E   2778    15/12/2012
Q   2878    15/12/2012
E   2778    15/12/2012
O   2350    15/12/2012
T   3138    15/12/2012
L   2913    15/12/2012
Q   2878    15/12/2012
D   3059    15/12/2012
K   2513    13/12/2012
K   1532    17/12/2012
AA  2018    17/12/2012
K   2883    17/12/2012
O   2350    17/12/2012
K   1532    17/12/2012
P   1541    17/12/2012
P   1541    17/12/2012
J   3168    17/12/2012
K   2816    17/12/2012
K   1532    17/12/2012
K   2883    17/12/2012
P   1541    17/12/2012
E   3120    14/12/2012
Q   2878    17/12/2012
H   3085    17/12/2012
G   1696    17/12/2012
H   3083    17/12/2012
G   2565    17/12/2012
G   2525    17/12/2012
K   1532    17/12/2012
G   2566    17/12/2012
T   3138    17/12/2012
D   3060    17/12/2012
D   3058    17/12/2012
P   1541    17/12/2012
H   3083    17/12/2012
H   3085    17/12/2012
D   3058    17/12/2012
G   3146    17/12/2012
E   3116    17/12/2012
K   2883    17/12/2012
H   3085    17/12/2012
E   3120    17/12/2012
O   2713    17/12/2012
H   3081    17/12/2012
E   2934    17/12/2012
B   2303    17/12/2012
O   3129    17/12/2012
D   3058    17/12/2012
D   3059    17/12/2012
E   54      17/12/2012
H   3085    17/12/2012
E   2934    17/12/2012
F   3099    17/12/2012
L   3144    17/12/2012
K   2883    17/12/2012
G   3146    17/12/2012
E   3120    17/12/2012
J   3168    17/12/2012
E   3150    17/12/2012
L   2913    17/12/2012
E   512     17/12/2012
G   2525    17/12/2012
E   2934    17/12/2012
Q   2878    17/12/2012
F   3099    17/12/2012
H   3081    17/12/2012
H   3083    17/12/2012
H   3085    17/12/2012
L   3144    17/12/2012
D   3056    17/12/2012
I   2909    17/12/2012
D   3059    17/12/2012
F   3099    17/12/2012
H   3081    17/12/2012
O   2713    17/12/2012
G   2807    17/12/2012
M   2817    17/12/2012
H   3082    17/12/2012
D   3060    17/12/2012
O   2713    17/12/2012
D   3056    17/12/2012
G   3166    17/12/2012
C   2242    16/12/2012
F   3099    17/12/2012
G   2566    17/12/2012
L   2913    16/12/2012
B   2301    17/12/2012
H   3081    17/12/2012
O   2713    17/12/2012
H   3082    17/12/2012
H   3085    17/12/2012
L   2913    16/12/2012
E   512     17/12/2012
E   3116    17/12/2012
G   2565    17/12/2012
D   3056    17/12/2012
I   2909    17/12/2012
H   3081    17/12/2012
Q   2878    17/12/2012
L   2913    16/12/2012
H   3081    17/12/2012
C   2707    17/12/2012
K   2883    17/12/2012
K   2883    17/12/2012
D   3058    18/12/2012
K   1532    18/12/2012
K   2883    18/12/2012
K   2816    18/12/2012
K   1532    18/12/2012
T   3138    18/12/2012
A   1217    08/12/2012
P   1541    18/12/2012
K   2816    18/12/2012
P   1541    18/12/2012
K   2883    18/12/2012
T   3152    18/12/2012
K   1532    18/12/2012
K   2816    18/12/2012
A   1217    18/12/2012
P   3028    18/12/2012
A   1217    17/12/2012
U   3091    18/12/2012
G   2525    18/12/2012
J   3168    18/12/2012
G   1311    18/12/2012
U   3014    18/12/2012
K   1532    18/12/2012
X   3131    18/12/2012
I   3179    18/12/2012
P   1541    18/12/2012
L   2913    18/12/2012
Q   2878    18/12/2012
X   3130    18/12/2012
I   2909    18/12/2012
K   1532    02/01/2013
H   3081    18/12/2012
K   2883    18/12/2012
K   1532    18/12/2012
P   1541    18/12/2012
D   3057    18/12/2012
O   2713    18/12/2012
D   3059    18/12/2012
D   3056    18/12/2012
G   2807    18/12/2012
G   3166    18/12/2012
D   3057    18/12/2012
H   3085    18/12/2012
K   1532    18/12/2012
I   2910    18/12/2012
U   3170    18/12/2012
D   3060    18/12/2012
E   52      18/12/2012
E   512     18/12/2012
O   1927    18/12/2012
M   1513    18/12/2012
T   3138    18/12/2012
D   3057    18/12/2012
O   3129    18/12/2012
K   1532    18/12/2012
G   3146    18/12/2012
I   1020    18/12/2012
D   3060    18/12/2012
C   2938    18/12/2012
K   2883    18/12/2012
E   2778    18/12/2012
D   3058    18/12/2012
I   1020    18/12/2012
O   2350    18/12/2012
E   3078    18/12/2012
H   3083    18/12/2012
C   2241    18/12/2012
H   3082    18/12/2012
G   2566    18/12/2012
H   3085    18/12/2012
Q   3047    18/12/2012
C   2242    18/12/2012
D   3058    18/12/2012
Q   2878    18/12/2012
E   52      18/12/2012
F   3099    18/12/2012
U   3171    18/12/2012
F   3096    18/12/2012
U   3091    18/12/2012
J   3168    18/12/2012
J   3168    18/12/2012
C   2241    18/12/2012
C   2707    18/12/2012
Q   3177    18/12/2012
H   3081    18/12/2012
D   3059    18/12/2012
L   2913    18/12/2012
H   3083    18/12/2012
G   2807    18/12/2012
U   3091    18/12/2012
E   3116    18/12/2012
G   2807    18/12/2012
H   3083    18/12/2012
Q   3075    18/12/2012
U   3170    18/12/2012
C   2241    18/12/2012
H   3081    18/12/2012
Q   2878    18/12/2012
Q   2878    18/12/2012
K   1532    18/12/2012
K   1532    18/12/2012
K   2816    18/12/2012
K   2816    18/12/2012
K   2816    18/12/2012
K   2883    18/12/2012
T   3138    19/12/2012
T   3139    19/12/2012
T   3152    19/12/2012
Q   3177    19/12/2012
E   52      19/12/2012
N   1858    19/12/2012
Q   3177    19/12/2012
U   3170    19/12/2012
G   3181    19/12/2012
G   3183    19/12/2012
E   52      19/12/2012
N   1858    19/12/2012
I   2698    19/12/2012
Q   3075    19/12/2012
T   3139    19/12/2012
G   2525    19/12/2012
N   1858    19/12/2012
E   52      19/12/2012
F   3099    19/12/2012
N   1858    19/12/2012
A   1217    12/12/2012
Q   3075    19/12/2012
A   1217    12/12/2012
D   3057    19/12/2012
F   3099    19/12/2012
L   2913    19/12/2012
E   3116    19/12/2012
E   52      19/12/2012
H   3081    19/12/2012
O   1927    19/12/2012
L   3144    19/12/2012
F   3096    19/12/2012
G   2807    19/12/2012
O   2350    19/12/2012
H   3083    19/12/2012
G   3146    19/12/2012
E   3116    19/12/2012
J   3168    19/12/2012
E   3153    19/12/2012
O   2680    19/12/2012
O   1927    19/12/2012
E   512     19/12/2012
G   2565    19/12/2012
B   2303    19/12/2012
H   3085    19/12/2012
N   1858    19/12/2012
F   3096    19/12/2012
L   2913    19/12/2012
H   3085    19/12/2012
D   3056    19/12/2012
G   2961    19/12/2012
Q   2878    19/12/2012
G   2565    19/12/2012
F   3096    19/12/2012
F   3095    19/12/2012
O   2771    19/12/2012
D   3058    19/12/2012
B   2303    19/12/2012
Q   3177    19/12/2012
H   3085    19/12/2012
O   2771    19/12/2012
F   3096    19/12/2012
U   3091    19/12/2012
H   3083    19/12/2012
Q   2878    19/12/2012
O   1927    19/12/2012
B   2303    19/12/2012
N   1858    19/12/2012
H   3085    19/12/2012
T   3138    19/12/2012
C   2242    19/12/2012
H   3082    19/12/2012
K   2883    19/12/2012
O   3129    20/12/2012
K   2513    20/12/2012
O   3129    20/12/2012
O   3129    17/12/2012
A   1217    17/12/2012
J   3168    20/12/2012
P   1541    04/01/2013
A   1217    18/12/2012
L   2913    20/12/2012
U   2985    20/12/2012
L   2913    20/12/2012
E   2778    20/12/2012
G   1696    20/12/2012
L   2913    20/12/2012
Q   2878    20/12/2012
D   3060    19/12/2012
G   2961    20/12/2012
D   3057    20/12/2012
D   3056    20/12/2012
E   2778    20/12/2012
O   2680    20/12/2012
G   2961    20/12/2012
D   3056    20/12/2012
T   3139    19/12/2012
D   3058    20/12/2012
L   2913    20/12/2012
E   3069    20/12/2012
N   1858    20/12/2012
E   52      20/12/2012
D   3056    20/12/2012
O   2680    20/12/2012
P   1541    04/01/2013
D   3060    19/12/2012
K   2883    20/12/2012
K   2816    20/12/2012
D   3057    20/12/2012
F   3099    20/12/2012
H   3083    20/12/2012
D   3056    20/12/2012
N   1858    20/12/2012
G   3181    20/12/2012
E   512     20/12/2012
D   3057    20/12/2012
G   2807    20/12/2012
H   3085    20/12/2012
E   3120    20/12/2012
D   3056    20/12/2012
G   2886    20/12/2012
B   2303    20/12/2012
N   1858    20/12/2012
H   3082    20/12/2012
D   3057    20/12/2012
T   3138    20/12/2012
G   2886    20/12/2012
X   3130    20/12/2012
N   1858    20/12/2012
F   3095    20/12/2012
Q   3177    20/12/2012
C   2242    20/12/2012
O   2771    20/12/2012
U   3091    20/12/2012
W   2968    18/12/2012
F   3095    20/12/2012
D   3060    20/12/2012
C   2242    20/12/2012
W   2968    17/12/2012
H   3083    20/12/2012
O   2680    20/12/2012
K   1532    03/01/2013
F   3099    20/12/2012
C   2707    20/12/2012
F   3095    20/12/2012
K   2513    14/12/2012
P   1541    21/12/2012
P   1541    21/12/2012
Tinnin
Junior Poster
160 posts since Jul 2012
Reputation Points: 8
Solved Threads: 1
Skill Endorsements: 2
5 Months Ago
0

My most recent attempt. Once again thwarted by Navicat's inability to create temporary tables for read-only access queries.

SELECT TeamID, PlayerID, MAX(Total) AS MaxTotal FROM (
    SELECT * FROM (
        SELECT TeamID,PlayerID, COUNT(*) AS Total
        FROM Scored
        WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
        GROUP BY PlayerID
    ) T
    WHERE NOT EXISTS ( SELECT PlayerID FROM ( # Top Scorer Query
                SELECT TeamID, PlayerID, MAX(Total) AS MaxTotal FROM (
                    SELECT TeamID, PlayerID, COUNT(*) AS Total
                    FROM Scored
                    WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
                    GROUP BY PlayerID
                    ORDER BY Total DESC
                ) T
                GROUP BYTeamID      
    )) 
    ORDER BY Total DESC
) T
GROUP BY TeamID

Giving me the error:

[Err] 1248 - Every derived table must have its own alias
Tinnin
Junior Poster
160 posts since Jul 2012
Reputation Points: 8
Solved Threads: 1
Skill Endorsements: 2
5 Months Ago
1

You cannot re-use T. That is the alias it is referring to, so rename two of them.

Sidenote: I don't think it's a Navicat issue that you cannot create temporary tables. It must be a priviliges problem. If you are absolutely sure it is a bug in Navicat report it.

pritaeas
Posting Prodigy
Moderator
9,533 posts since Jul 2006
Reputation Points: 1,194
Solved Threads: 1,494
Skill Endorsements: 98
5 Months Ago
0

Thanks. Done. It is giving me no results though.

Tinnin
Junior Poster
160 posts since Jul 2012
Reputation Points: 8
Solved Threads: 1
Skill Endorsements: 2
5 Months Ago
1

One missing alias on line 17 between the parenthesis.

pritaeas
Posting Prodigy
Moderator
9,533 posts since Jul 2006
Reputation Points: 1,194
Solved Threads: 1,494
Skill Endorsements: 98
5 Months Ago
0

My failing query :(

SELECT TeamID, PlayerID, MAX(Total) AS MaxTotal FROM (
    SELECT * FROM (
        SELECT TeamID, PlayerID, COUNT(*) AS Total
        FROM Scored
        WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
        GROUP BY PlayerID
    ) T
    WHERE NOT EXISTS ( SELECT PlayerID FROM ( # Top Scorer Query
                SELECT TeamID, PlayerID, MAX(Total) AS MaxTotal FROM (
                    SELECT TeamID, PlayerID, COUNT(*) AS Total
                    FROM Scored
                    WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
                    GROUP BY PlayerID
                    ORDER BY Total DESC
                ) S
                GROUP BY TeamID     
    )Q ) 
    ORDER BY Total DESC
) R
GROUP BY TeamID
Tinnin
Junior Poster
160 posts since Jul 2012
Reputation Points: 8
Solved Threads: 1
Skill Endorsements: 2
5 Months Ago
2

Done:

SELECT TeamID, PlayerID, MAX(Total) AS MaxTotal FROM (
    SELECT * FROM (
        SELECT TeamID, PlayerID, COUNT(*) AS Total
        FROM Scored
        WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
        GROUP BY PlayerID
    ) T
    WHERE NOT EXISTS ( SELECT NULL FROM ( 
                SELECT TeamID, PlayerID, MAX(Total) AS MaxTotal FROM (
                    SELECT TeamID, PlayerID, COUNT(*) AS Total
                    FROM Scored
                    WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
                    GROUP BY PlayerID
                    ORDER BY Total DESC
                ) S
                GROUP BY TeamID     
    ) Q
    WHERE T.PlayerID = Q.PlayerID
        ) 
    ORDER BY Total DESC
) R
GROUP BY TeamID
Tinnin
Junior Poster
160 posts since Jul 2012
Reputation Points: 8
Solved Threads: 1
Skill Endorsements: 2
Question Answered as of 5 Months Ago by pritaeas

This question has already been solved: Start a new discussion instead

Post: Markdown Syntax: Formatting Help
 
You
Web Development > Databases > MySQL
 
© 2013 DaniWeb® LLC
Page generated in 0.6044 seconds using 2.75MB