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
So you want to get the row with the highest OrderField per group? I’d do it this way: SELECT t1.* FROM `Table` AS t1 LEFT OUTER JOIN `Table` AS t2 ON t1.GroupId = t2.GroupId AND t1.OrderField < t2.OrderField WHERE t2.GroupId IS NULL ORDER BY t1.OrderField; // not needed! (note by Tomas) (EDIT by Tomas: If … Read more
Use: SELECT m.yr, COUNT(*) AS num_movies FROM MOVIE m JOIN CASTING c ON c.movieid = m.id JOIN ACTOR a ON a.id = c.actorid AND a.name=”John Travolta” GROUP BY m.yr ORDER BY num_movies DESC, m.yr DESC Ordering by num_movies DESC will put the highest values at the top of the resultset. If numerous years have the … Read more
This is a perfect use-case for DISTINCT ON – a Postgres specific extension of the standard DISTINCT: SELECT DISTINCT ON (category) id — , category, date — any other column (expression) from the same row FROM tbl ORDER BY category, date DESC; Careful with descending sort order. If the column can be NULL, you may … Read more
Use the aggregate MAX(signin) grouped by id. This will list the most recent signin for each id. SELECT id, MAX(signin) AS most_recent_signin FROM tbl GROUP BY id To get the whole single record, perform an INNER JOIN against a subquery which returns only the MAX(signin) per id. SELECT tbl.id, signin, signout FROM tbl INNER JOIN … Read more
The most efficient way is to use Postgres’ distinct on operator select distinct on (id) id, date, another_info from the_table order by id, date desc; If you want a solution that works across databases (but is less efficient) you can use a window function: select id, date, another_info from ( select id, date, another_info, row_number() … Read more
For the sake of completeness, here’s another possible solution: SELECT sensorID,timestamp,sensorField1,sensorField2 FROM sensorTable s1 WHERE timestamp = (SELECT MAX(timestamp) FROM sensorTable s2 WHERE s1.sensorID = s2.sensorID) ORDER BY sensorID, timestamp; Pretty self-explaining I think, but here’s more info if you wish, as well as other examples. It’s from the MySQL manual, but above query works … Read more
Query: SQLFIDDLEExample SELECT t1.* FROM lms_attendance t1 WHERE t1.time = (SELECT MAX(t2.time) FROM lms_attendance t2 WHERE t2.user = t1.user) Result: | ID | USER | TIME | IO | ——————————– | 2 | 9 | 1370931664 | out | | 3 | 6 | 1370932128 | out | | 5 | 12 | 1370933037 | … Read more
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
Here is one way to do this, using UNION ALL (See SQL Fiddle with Demo). This works with two groups, if you have more than two groups, then you would need to specify the group number and add queries for each group: ( select * from mytable where `group` = 1 order by age desc … Read more