Try this: Select u.[username] ,u.[ip] ,q.[time_stamp] From [users] As u Inner Join ( Select [username] ,max(time_stamp) as [time_stamp] From [users] Group By [username]) As [q] On u.username = q.username And u.time_stamp = q.time_stamp
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
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: https://wiki.postgresql.org/wiki/Aggregate_Mode https://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE
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
The columns in the result set of a select query with group by clause must be: an expression used as one of the group by criteria , or … an aggregate function , or … a literal value So, you can’t do what you want to do in a single, simple query. The first thing … Read more
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
There are 2 solutions: 1.sort_values and aggregate head: df1 = df.sort_values(‘score’,ascending = False).groupby(‘pidx’).head(2) print (df1) mainid pidx pidy score 8 2 x w 12 4 1 a e 8 2 1 c a 7 10 2 y x 6 1 1 a c 5 7 2 z y 5 6 2 y z 3 3 … Read more
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
One way to do this that correctly uses group by: select l.* from table l inner join ( select m_id, max(timestamp) as latest from table group by m_id ) r on l.timestamp = r.latest and l.m_id = r.m_id order by timestamp desc How this works: selects the latest timestamp for each distinct m_id in the … Read more
You have to use side effecting variables for this SELECT profilename, name FROM ( SELECT m.profilename, s.name, @r:=case when @g=m.profilename then @r+1 else 1 end r, @g:=m.profilename FROM (select @g:=null,@r:=0) n cross join menus m left join menuitems s on m.menuid = s.menuid ) X WHERE r <= 5