GROUP BY with MAX(DATE) [duplicate]
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
greatest-n-per-group
Pandas get topmost n records within each group
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
Select top 10 records for each category
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
ROW_NUMBER() in MySQL
There is no ranking functionality in MySQL. The closest you can get is to use a variable: SELECT t.*, @rownum := @rownum + 1 AS rank FROM YOUR_TABLE t, (SELECT @rownum := 0) r so how would that work in my case? I’d need two variables, one for each of col1 and col2? Col2 would … Read more
Get records with max value for each group of grouped SQL results
The correct solution is: SELECT o.* FROM `Persons` o # ‘o’ from ‘oldest person in group’ LEFT JOIN `Persons` b # ‘b’ from ‘bigger age’ ON o.Group = b.Group AND o.Age < b.Age WHERE b.Age is NULL # bigger age not found How it works: It matches each row from o with all the rows … Read more
how do I query sql for a latest record date for each user
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
SQL join: selecting the last records in a one-to-many relationship
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
Using LIMIT within GROUP BY to get N results per group?
You could use GROUP_CONCAT aggregated function to get all years into a single column, grouped by id and ordered by rate: SELECT id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year FROM yourtable GROUP BY id Result: ———————————————————– | ID | GROUPED_YEAR | ———————————————————– | p01 | 2006,2003,2008,2001,2007,2009,2002,2004,2005,2000 | | p02 | 2001,2004,2002,2003,2000,2006,2007 | ———————————————————– And then … Read more
Fetch the rows which have the Max value for a column for each distinct value of another column
I see many people use subqueries or else window functions to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it should work in any brand of RDBMS. SELECT t1.* FROM mytable t1 LEFT OUTER JOIN mytable t2 ON (t1.UserId = t2.UserId … Read more
Get top 1 row of each group
;WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn FROM DocumentStatusLogs ) SELECT * FROM cte WHERE rn = 1 If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead As for normalised or … Read more