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
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
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
You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields: SELECT tt.* FROM topten tt INNER JOIN (SELECT home, MAX(datetime) AS MaxDateTime FROM topten GROUP BY home) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime
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
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
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
;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
DISTINCT ON is typically simplest and fastest for this in PostgreSQL. (For performance optimization for certain workloads see below.) SELECT DISTINCT ON (customer) id, customer, total FROM purchases ORDER BY customer, total DESC, id; Or shorter (if not as clear) with ordinal numbers of output columns: SELECT DISTINCT ON (2) id, customer, total FROM purchases … Read more
At first glance… All you need is a GROUP BY clause with the MAX aggregate function: SELECT id, MAX(rev) FROM YourTable GROUP BY id It’s never that simple, is it? I just noticed you need the content column as well. This is a very common question in SQL: find the whole data for the row … Read more