You should find out last timestamp values in each group (subquery), and then join this subquery to the table – SELECT t1.* FROM messages t1 JOIN (SELECT from_id, MAX(timestamp) timestamp FROM messages GROUP BY from_id) t2 ON t1.from_id = t2.from_id AND t1.timestamp = t2.timestamp;
Here’s the fast data.table way: bdt[bdt[, .I[g == max(g)], by = id]$V1] This avoids constructing .SD, which is the bottleneck in your expressions. edit: Actually, the main reason the OP is slow is not just that it has .SD in it, but the fact that it uses it in a particular way – by calling … Read more
The shortest (and possibly fastest) query would be with DISTINCT ON, a PostgreSQL extension of the SQL standard DISTINCT clause: SELECT DISTINCT ON (1) id, count, year FROM tbl ORDER BY 1, 2 DESC, 3; The numbers refer to ordinal positions in the SELECT list. You can spell out column names for clarity: SELECT DISTINCT … Read more
Use: SELECT tbl.* FROM TableName tbl INNER JOIN ( SELECT Id, MIN(Point) MinPoint FROM TableName GROUP BY Id ) tbl1 ON tbl1.id = tbl.id WHERE tbl1.MinPoint = tbl.Point
Putting the subquery in the WHERE clause and restricting it to n.control_number means it runs the subquery many times. This is called a correlated subquery, and it’s often a performance killer. It’s better to run the subquery once, in the FROM clause, to get the max date per control number. SELECT n.* FROM tblpm n … Read more
Are you looking for the max date for each model? SELECT model, max(date) FROM doc GROUP BY model If you’re looking for all models matching the max date of the entire table… SELECT model, date FROM doc WHERE date IN (SELECT max(date) FROM doc) [— Added —] For those who want to display details from … Read more
Starting from Django 1.11 and thanks to Subquery and OuterRef, we can finally build a latest-per-group query using the ORM. hottest_cakes = Cake.objects.filter( baked_at=Subquery( (Cake.objects .filter(bakery=OuterRef(‘bakery’)) .values(‘bakery’) .annotate(last_bake=Max(‘baked_at’)) .values(‘last_bake’)[:1] ) ) ) #BONUS, we can now use this for prefetch_related() bakeries = Bakery.objects.all().prefetch_related( Prefetch(‘cake_set’, queryset=hottest_cakes, to_attr=”hottest_cakes” ) ) #usage for bakery in bakeries: print ‘Bakery … Read more
distinct is not a function. It always operates on all columns of the select list. Your problem is a typical “greatest N per group” problem which can easily be solved using a window function: select … from ( select IDNo, FirstName, LastName, …., row_number() over (partition by lower(idno) order by firstname) as rn from people … Read more
For best read performance you need a multicolumn index: CREATE INDEX log_combo_idx ON log (user_id, log_date DESC NULLS LAST); To make index only scans possible, add the otherwise not needed column payload in a covering index with the INCLUDE clause (Postgres 11 or later): CREATE INDEX log_combo_covering_idx ON log (user_id, log_date DESC NULLS LAST) INCLUDE … Read more
SELECT group,MAX(date) as max_date FROM table WHERE checks>0 GROUP BY group That works to get the max date..join it back to your data to get the other columns: Select group,max_date,checks from table t inner join (SELECT group,MAX(date) as max_date FROM table WHERE checks>0 GROUP BY group)a on a.group = t.group and a.max_date = date Inner … Read more