I’ve tried this query, but the result is just every “max_deltakelser” and not only the ones that have the highes value? Any idea about what’s wrong?
SELECT sptSyklistID, MAX( deltakelser ) AS max_deltakelser
FROM (
SELECT sptSyklistID, COUNT( sptSyklistID ) AS deltakelser
FROM mck_syklist_pa_tur , mck_syklist
WHERE syklistID = sptSyklistID GROUP BY sptSyklistID)
AS syklister GROUP BY sptSyklistID
Cato
That looks like a syntax that would throw an error (if so: which error?). What does your database structure look like, and what information are you trying to extract?
– Table mck_syklist_pa_tur
sptSyklistID
sptTurID (same as turID in the table mck_tur)
sptKlatrePlassering
sptSpurtPlassering
sptKlatrePoeng
sptSpurtPoeng
– Table for tabell mck_syklist
syklistID (same as sptSyklistID in mck_syklist_pa_tur)
syklistFornavn
syklistEtternavn
– Table for tabell mck_tur
turID
turYEAR
turRound
I’m trying to the does “turID” with the highest appearences (with the count function) withing for example a spesific year.
An example
sptSyklistID - sptTurID - turYEAR
1 - 1 - 2008
2 - 1 - 2008
3 - 1 - 2008
1 - 2 - 2008
2 - 3 - 2008
1 - 4 - 2008
2 - 4 - 2008
From this list you can see that sptSyklistID with the number 1 and 2 have both been on 3 trips each. I want to extract them, and only them, I don’t want to get out sptSyklistID=3 since that number only appears one time.
Was this a bit more clear, or still “fussy”?
Nope, makes sense to me now
What you need to use is GROUP BY in your SQL query. I’m thinking something along these lines:
SELECT count(m.sptSyklistID) FROM mck_syklist_pa_tur AS m JOIN (mck_tur AS t) ON (m.sptTurID = t.turID AND t..turYEAR = '2008') GROUP BY m.sptSyklistIDI’m not too fluent in using GROUP BY or JOIN, but you can read up on the specifics of those in your database’s manual. Try this and see if it works.
But, where is the MAX function. I tried this code briefly, but got an error about mysql not finding m.sptSyklistID in the field list.
I just used the table columns that you gave me, so if you get an ‘unknown column’ error, then check your table column names and fill in the correct one. You’re right that I missed the MAX function, but I’m not too sure where to input this in the query, as MAX is usually used for finding the highest value in a column, not the highest number of occurrances. The problem is that you’ve got things spread out over multiple tables. You could split this up over multiple queries, to keep things from getting too complicated.