I need to list all the users who have created a vote, a user can create many votes and that user belongs to a company.
I'm grouping them because I need the last vote per user, but I need to order the voting in progress first (4) and then the votes completed (5)
select u.nombre, v.contexto, v.fecha from users u inner join empresas e on e.id = u.empresa_id inner join votacion v on u.id = v.user_id where v.fecha <= now() and ( v.estado = "4" or v.estado = "5" ) group by u.id order by v.estado asc, v.fecha desc, v.id desc
The problem I have is in the context and date column, because it brings me the result of the first record it finds and I need the last, how can I sort the records before grouping them?
The following query fulfills my objective
select count(v.user_id) as total, u.nombre, v.contexto, v.fecha, v.estado from users u inner join empresas e on e.id = u.empresa_id inner join ( select fecha, user_id, contexto, estado from votacion where fecha <= now() AND (estado = "4" or estado = "5") order by estado, fecha desc, id desc ) v on u.id = v.user_id group by u.id order by v.estado
Would it be the right way to do it? or is this an overload for the database?