MySQL 8.0 now supports windowing functions, like almost all popular SQL implementations. With this standard syntax, we can write greatest-n-per-group queries: WITH ranked_messages AS ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn FROM messages AS m ) SELECT * FROM ranked_messages WHERE rn = 1; This and other approaches … Read more
select t.username, t.date, t.value from MyTable t inner join ( select username, max(date) as MaxDate from MyTable group by username ) tm on t.username = tm.username and t.date = tm.MaxDate
I don’t have an oracle instance handy right now so I have not tested this: select * from (select emp_id, name, occupation, rank() over ( partition by occupation order by emp_id) rank from employee) where rank <= 3 Here is a link on how rank works: http://www.psoug.org/reference/rank.html
This is an example of the greatest-n-per-group problem that has appeared regularly on StackOverflow. Here’s how I usually recommend solving it: SELECT c.*, p1.* FROM customer c JOIN purchase p1 ON (c.id = p1.customer_id) LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND (p1.date < p2.date OR (p1.date = p2.date AND p1.id < p2.id))) … Read more
This is also possible without subquery: SELECT DISTINCT id ,max(round) OVER (PARTITION BY id) AS round ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score FROM SCORES WHERE id IN (1,2,3) ORDER BY id; Returns exactly what you asked for. The crucial point is that DISTINCT is applied after window functions. SQL Fiddle. … Read more
Did you try df.groupby(‘id’).head(2) Output generated: id value id 1 0 1 1 1 1 2 2 3 2 1 4 2 2 3 7 3 1 4 8 4 1 (Keep in mind that you might need to order/sort before, depending on your data) EDIT: As mentioned by the questioner, use df.groupby(‘id’).head(2).reset_index(drop=True) to remove … Read more
If you are using SQL 2005 you can do something like this… SELECT rs.Field1,rs.Field2 FROM ( SELECT Field1,Field2, Rank() over (Partition BY Section ORDER BY RankCriteria DESC ) AS Rank FROM table ) rs WHERE Rank <= 10 If your RankCriteria has ties then you may return more than 10 rows and Matt’s solution may … Read more
This is the greatest-n-per-group problem, and it’s a very common SQL question. Here’s how I solve it with outer joins: SELECT i1.* FROM item i1 LEFT OUTER JOIN item i2 ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id) GROUP BY i1.item_id HAVING COUNT(*) < 4 ORDER BY category_id, date_listed; I’m assuming the primary key of … Read more
SELECT train, dest, time FROM ( SELECT train, dest, time, RANK() OVER (PARTITION BY train ORDER BY time DESC) dest_rank FROM traintable ) where dest_rank = 1
Try this: result <- df %>% group_by(A, B) %>% filter(value == max(value)) %>% arrange(A,B,C) Seems to work: identical( as.data.frame(result), ddply(df, .(A, B), function(x) x[which.max(x$value),]) ) #[1] TRUE As pointed out in the comments, slice may be preferred here as per @RoyalITS’ answer below if you strictly only want 1 row per group. This answer will … Read more