the query I want to make is about the selection of a song with certain specifications, to make a playlist, it is generated as the songs are played, the query is something like selecting the next song. In the table I have a logical data like, "reproduced" where it turns to true when it is played and "ultimareproduccion" where the DATETIME is placed at the time of playback. Here is the schema of the database:
CREATE TABLE "albums" ( 'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 'nombre' TEXT );
CREATE TABLE "musica" ( 'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 'nombreyruta' TEXT NOT NULL, 'anulado' NUMERIC NOT NULL DEFAULT 0, 'album' INTEGER, 'reproducido' INTEGER NOT NULL DEFAULT 0, 'ultimareproduccion' TEXT );
CREATE TABLE "reproduccion" ( 'Id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 'idalbum' INTEGER NOT NULL DEFAULT 0, 'habilitado' INTEGER NOT NULL DEFAULT 1 )
I would need that in the selection of the next topic, it is based on a SELECT query, that it chooses the next topic to be reproduced in the following way, that it has the data REPRODUCTION = 0, and that it avoids selecting the ones that have been reproduced recently. time, and also, that within this in a percentage you can perform a Ramdom, or random selection. An example, the theme of reproduction ordered from highest to lowest in "ULTIMAREPRODUCCION" is requested and that 40% of these older ones can be randomly selected. The main idea is that the playlist is not repetitive, that is, that the list is not always the same, and that the songs are not heard one after the other.
Here I leave the selection that I have to previous ones, what it does is select all the themes that can be chosen for its reproduction:
SELECT musica.id from musica inner join reproduccion on musica.album = reproduccion.idalbum WHERE musica.reproducido = 0;
As you can see, the reproduction is not carried in the database, but it is done when you ask with a SELECT which is the next song to play. what would it take to solve this question!
I hope you can help me thank you very much