Hello I have a table that has a value ID
and other values among which is nombre
.
###############
# ID # Nombre #
###############
# 4 # Juan #
###############
# 3 # Juan #
###############
# 2 # Juan #
###############
# 1 # Martin #
###############
# 0 # Juan #
###############
Is it possible through a MySql query to obtain the occurrences followed by the most recent name (in this case Juan)?
In other words, from the most recent name entered in the table (in this case Juan
) its occurrences are counted until another value interrupts the count (in this case Martin
).
In the example table the return value should be 3 because the last name entered in the table was Juan and it had 3 occurrences before the name Martin appeared.
Try this Query
To see the working example click here
Let's imagine this is your table:
So the last name comes out of the query
You can get, in a query, the maximum ID of the table together with the maximum value other than the last name:
and that returns
So the desired result is obtained from the difference between the two.
Now, what happens when someone removes an intermediate value?
The result should be 2, but with the query that I showed you, the rest of the IDs would still say 3. You have to try another solution.
For example, you can select all the records where the ID is greater than the largest ID other than the last name (it's already sounding far-fetched)
and that returns
So it's a thing to do
And with that you get