How to get the last registration if I use group by

Question

How to get the last registration if I use group by

Navigation

#1 by (0 votes)

5

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?

@UPDATE

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?

mysql

asked by ccorrea 08.10.2018 в 03:19

source

1 answer

laravel 5.5, multiple drivers in one or more views? Facebook Login

score 0 · Answer 1

I'm sure someone can give you a better solution than this one, but it works: What I would do is first create a view that gives me all the maximum id of the votes of each user, eg:

CREATE VIEW 'vw_votacionmaxima' AS
    SELECT 
        'users'.'id' AS 'user_id',
        MAX('votacion'.'id') AS 'votacionmax_id'
    FROM
        ('votacion'
        JOIN 'users' ON (('votacion'.'user_id' = 'users'.'id')))
    GROUP BY 'votacion'.'user_id';

With this view (which is only created once in your database) your select gives you the result you are looking for:

SELECT
    users.nombre,
    votacion.contexto,
    votacion.fecha 
FROM
    vw_votacionmaxima
    INNER JOIN users ON vw_votacionmaxima.user_id = users.id
    INNER JOIN votacion ON vw_votacionmaxima.votacionmax_id = votacion.id 
WHERE
    votacion.fecha <= now( ) 
    AND ( votacion.estado = "4" OR votacion.estado = "5" ) 
ORDER BY
    vw_votacionmaxima.votacionmax_id DESC,
    votacion.fecha DESC

I hope you try and it works!