Could anyone explain to me what's wrong with my syntax / usage of the RANK function?
Problem situation: "Make a ranking of songs per band, so that the songs of greatest occupy the first places. Display band and music name and ranking position. "
SELECT MU.NOME, BA.NOME
RANK() OVER (PARTITION BY MU.TEMPO_DURACAO ORDER BY BA.NOME)
FROM MUSICA MU, BANDAS_E_ARTISTAS BA, BANDA BD
WHERE BA.ID = BD.ID
Well it seems that your join is wrong, you are joining music, bands and artists and bands, but it is only doing the join between the bands and the artists, but the musics are without anything in the where clause. The ideal would be to have a MU.id_banda = BD.id or something of the type.
As for the rank, it asks to break by band and order by time, soon should be something like this:
SELECT MU.NOME, BA.NOME,
RANK() OVER (PARTITION BY BA.NOME ORDER BY MU.TEMPO_DURACAO DESC)
FROM MUSICA MU, BANDAS_E_ARTISTAS BA, BANDA BD
WHERE BA.ID = BD.ID AND MU.BANDA_ID = BD.ID