How to select data where a field has a min value in MySQL?
this will give you result that has the minimum price on all records. SELECT * FROM pieces WHERE price = ( SELECT MIN(price) FROM pieces ) SQLFiddle Demo
greatest-n-per-group
Select corresponding to row from the same table SQL Server
I’m not really smart from your description, however, the result can be achieved using the following query select your_table.* from your_table join ( select BlilShortName, max(billversion) bmax from your_table group by BlilShortName ) t on your_table.billversion = t.bmax and your_table.BlilShortName = t.BlilShortName From my experience it can be faster in some cases when compared to … Read more
Laravel – Get the last entry of each UID type
This is easy enough to handle in MySQL: SELECT t1.* FROM yourTable t1 INNER JOIN ( SELECT UID, MAX(created_at) AS max_created_at FROM yourTable GROUP BY UID ) t2 ON t1.UID = t2.UID AND t1.created_at = t2.max_created_at; Translating this over to Eloquent would be some work, but hopefully this gives you a good starting point. Edit: … Read more
PostgreSQL: top n entries per item in same table
SELECT * FROM ( SELECT uid, title, amount, maker, widgets, rank() over (partition by maker order by amount desc) as rank FROM entry ) t WHERE rank <= 3
MySQL SELECT most frequent by group
SELECT t1.* FROM (SELECT tag, category, COUNT(*) AS count FROM tags INNER JOIN stuff USING (id) GROUP BY tag, category) t1 LEFT OUTER JOIN (SELECT tag, category, COUNT(*) AS count FROM tags INNER JOIN stuff USING (id) GROUP BY tag, category) t2 ON (t1.tag = t2.tag AND (t1.count < t2.count OR t1.count = t2.count AND … Read more
Selecting all corresponding fields using MAX and GROUP BY
without a single primary key field, I think your best bet is: select * from deal_status inner join (select deal_id as did, max(timestamp) as ts from deal_status group by deal_id) as ds on deal_status.deal_id = ds.did and deal_status.timestamp = ds.ts this still won’t work if you allow having two different statuses for the same product … Read more
SQL query to get most recent row for each instance of a given key
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
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: https://wiki.postgresql.org/wiki/Aggregate_Mode https://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE
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