I have this table with this data (name and surname of actors)
The web has to show the data (name and surname) of an actor and I have two buttons (previous and next) to consult the previous or next actor
I have the query like this:
SELECT id_ac, nombre, apellidos FROM actores WHERE nombre < (SELECT nombre FROM actores WHERE id_ac = 408) ORDER BY nombre DESC LIMIT 1
So, if I do a test to get the previous record corresponding to the actor with id 408, I get as a result Albert with id 49, instead of Alberto with id 489, which would be just the previous one, since there is more than one actor named Alberto
How can I make the query?
Thank you!
I suggest you use a field in which you keep the order updated, if you read more than you write, which is usually the case, it will hardly affect performance:
Every time you perform a
INSERT
,DELETE
orUPDATE
in that table, you should update the content of that field with these two instructions that you must execute within the same session, from a trigger or from the program that launches the add, delete or modify query.As if it were a type query
SELECT
, with aUPDATE
you can also make use ofJOIN
which, in this case, joins the tableactores
with an ordered version of itself. If you run it separately, you'll see exactly what it contains:The reserved word
SET
is part of theUPDATE
. You will be more used to seeing it like this:In this way it will be very easy for you to locate both the previous and the following:
I have done the tests with this dataset:
Leave me any questions in the comments.
If they have the same name, you must bring the ones with the lowest surname, in addition to excluding the reference id:
It's a self-join , where
ar
is for the referenced actor.As David comments, you need to have an identifier per row to make it easier for you to calculate the previous or the next one.
...with something like this you can know in which register you are and which one you are going to.