Top 10 Showclosers van concerten
Ik heb o.a. de volgende tabellen:
bt_songs: de nummers (id, titel, songtekst, releasedatum etc)
bt_live: de concerten (id, datumconcert, plaats etc)
bt_concertsongs: de setlijst -> koppeling tussen het concert en de gespeelde nummers (id, concertid, volgorde, songid)
Het veld 'volgorde' geeft aan het hoeveelste nummer het van de setlijst is. Nummer 1 is het eerste nummer van dat concert, het laatste nummer van een concert varieert. De ene keer worden er 26 nummers gespeeld, de andere keer 34.
Voorbeeld van een een stukje van de bt_concertsongs (id, concertid, volgorde, songid):
1 12 1 41
2 12 2 267
3 12 3 206
4 12 4 30
5 12 5 8
6 12 6 45
7 12 7 271
8 12 8 33
9 12 9 34
10 12 10 67
11 12 11 80
12 12 12 269
13 12 13 216
14 12 14 70
15 12 15 20
16 12 16 218
17 12 17 275
18 12 18 276
19 12 19 25
20 12 20 272
21 12 21 279
22 12 22 174
23 12 23 280
24 12 24 21
25 12 25 72
26 12 26 75
27 12 27 263
Showopeners:
SELECT bt_songs.titel, songid, count(songid) FROM `bt_concertsongs` Inner Join bt_songs On bt_songs.id = bt_concertsongs.songid WHERE `volgorde` = 1 GROUP BY songid Order By count(songid) DESC LIMIT 0 , 10
Ik wil dus een top 10 maken vanuit deze tabel waar per concert de waarde van 'volgorde' het hoogste getal is bij dat concertid.
Ik weet even niet hoe ik dit zou moeten doen. Heeft iemand suggesties?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT
song_id,
s.song_title,
COUNT(*) aantal
FROM
(SELECT
concert_id,
MAX(volgorde) volgorde
FROM
bt_concertsongs
GROUP_BY
concert_id
) t
JOIN
bt_concertsongs
USING (concert_id, volgorde)
JOIN
bt_songs s
USING (song_id)
GROUP BY
song_id, s.song_title
ORDER BY
aantal DESC LIMIT 10
song_id,
s.song_title,
COUNT(*) aantal
FROM
(SELECT
concert_id,
MAX(volgorde) volgorde
FROM
bt_concertsongs
GROUP_BY
concert_id
) t
JOIN
bt_concertsongs
USING (concert_id, volgorde)
JOIN
bt_songs s
USING (song_id)
GROUP BY
song_id, s.song_title
ORDER BY
aantal DESC LIMIT 10
Edit:
Oops, group by vergeten in de subquery
Gewijzigd op 26/08/2014 20:57:37 door Ger van Steenderen
Hier nog mijn script voor het eerste nummer per concert:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT bt_songs.titel, songid, count(songid)
FROM `bt_concertsongs` Inner Join bt_songs On bt_songs.id = bt_concertsongs.songid
WHERE `volgorde` = 1
GROUP BY songid Order By count(songid)
DESC LIMIT 0 , 10
FROM `bt_concertsongs` Inner Join bt_songs On bt_songs.id = bt_concertsongs.songid
WHERE `volgorde` = 1
GROUP BY songid Order By count(songid)
DESC LIMIT 0 , 10
Als ik het goed begrijp probeer je via een soort "subquery" eerst alle nummers te achterhalen per "concertid" die de hoogste waarde voor "volgorde" hebben?
In je script had ik concert_id al gewijzigd naar concertid en song_id naar songid.
Gewijzigd op 27/08/2014 20:32:19 door Eelco Klomp
Zo ongeveer, in de subquery wordt bepaalt wat het hoogste volgnummer per concert is.
Daarna join ik nogmaals dezelfde tabel op concertid én volgnummer om te achterhalen welke song dat is.
Daarna join ik songs om de title etc. te achterhalen.
Echter ik gebruik USING (song_id), maar dat kan alleen als de kolommen in beide tabellen dezelfde naam hebben, wat volgens mij bij jou niet het geval is.
Dit moet je dan wijzigingen in ON .... = ....