How to select the most recent set of dated records from a mysql table

This solution was updated recently. Comments below may be outdated This can query may perform well, because there are no joins. SELECT * FROM ( SELECT *,if(@last_method=method,0,1) as new_method_group,@last_method:=method FROM rpc_responses ORDER BY method,timestamp DESC ) as t1 WHERE new_method_group=1; Given that you want one resulting row per method this solution should work, using mysql … Read more

Get most common value for each value of another column in SQL

It is now even simpler: PostgreSQL 9.4 introduced the mode() function: select mode() within group (order by food_id) from munch group by country returns (like user2247323’s example): country | mode ————– GB | 3 US | 1 See documentation here:

Select row by max value in group in a pandas dataframe

A standard approach is to use groupby(keys)[column].idxmax(). However, to select the desired rows using idxmax you need idxmax to return unique index values. One way to obtain a unique index is to call reset_index. Once you obtain the index values from groupby(keys)[column].idxmax() you can then select the entire row using df.loc: In [20]: df.loc[df.reset_index().groupby([‘F_Type’])[‘to_date’].idxmax()] Out[20]: … Read more

mysql select top n max values

For n=2 you could SELECT max(column1) m FROM table t GROUP BY column2 UNION SELECT max(column1) m FROM table t WHERE column1 NOT IN (SELECT max(column1) WHERE column2 = t.column2) for any n you could use approaches described here to simulate rank over partition. EDIT: Actually this article will give you exactly what you need. … Read more

Mysql select distinct

DISTINCT is not a function that applies only to some columns. It’s a query modifier that applies to all columns in the select-list. That is, DISTINCT reduces rows only if all columns are identical to the columns of another row. DISTINCT must follow immediately after SELECT (along with other query modifiers, like SQL_CALC_FOUND_ROWS). Then following … Read more