New answers tagged

2

This is a classic greatest-n-per-group problem. I would urge you to look at that link (and also here - search for "Within-group quotas (Top N per group)") to learn about this important SQL functionality! That second link's entire page is a great resource for learning SQL generally - plus it's specific to MySQL! In order to solve your particular problem (n = ...


0

If you want strict semantics you need to double join: Once to find the selector (i.e. the most current rating) and a second time to fetch the rating: SELECT baseview.*. ratings.rating FROM ( SELECT players.*, MAX(`date`) AS ratingdate FROM players INNER JOIN ratings ON ratings.player_id=players.id GROUP BY players.id ) AS baseview ...


1

One possible approach is to number rows appropriately using ROW_NUMBER(): SELECT id_log, fecha_gestion, nitsec FROM ( SELECT id_log, fecha_gestion, nitsec, ROW_NUMBER() OVER (PARTITION BY nitsec ORDER BY fecha_gestion DESC) AS rn FROM table ) t WHERE rn = 1


1

Because no sample data was given, the next query is untested: SELECT ID_log, fecha_gestion, nitsec FROM table t1 WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.nitsec = t1.nitsec and t2.fecha_gestion < t1.fecha_gestion)


0

Test this: select sum_duration, app_name, app_id, user_id from ( select get_data.*, if( @user_id = get_data.user_id, @rownum := @rownum + 1, @rownum := 1 + least(0, @user_id := get_data.user_id)) row_number from ( select sum(duration) sum_duration, ...


0

You can use the 2nd query as a subquery in this way: SELECT usr1.name, posts.id, text_posts.text, posts.location posts.createdate COUNT(*) AS total, (SELECT COUNT(user_id) FROM follows INNER JOIN users usr2 ON usr2.id = follows.user_id WHERE usr2.id = usr1.id GROUP BY ...


Top 50 recent answers are included