You can use analytic functions SELECT * FROM (SELECT c.*, rank() over (partition by user_id order by ts desc) rnk FROM comments c) WHERE rnk = 1 Depending on how you want to handle ties (if there can be two rows with the same user_id and ts), you may want to use the row_number or … Read more
Quite a clear question 🙂 SELECT t1.* FROM purchases t1 LEFT JOIN purchases t2 ON t1.address_id = t2.address_id AND t1.purchased_at < t2.purchased_at WHERE t2.purchased_at IS NULL ORDER BY t1.purchased_at DESC And most likely a faster approach: SELECT t1.* FROM purchases t1 JOIN ( SELECT address_id, max(purchased_at) max_purchased_at FROM purchases GROUP BY address_id ) t2 ON … Read more
Your definition: activity from group B always takes place after activity from group A. .. logically implies that there is, per user, 0 or 1 B activity after 1 or more A activities. Never more than 1 B activities in sequence. You can make it work with a single window function, DISTINCT ON and CASE, … Read more
SELECT * FROM ( SELECT uid, title, amount, maker, widgets, rank() over (partition by maker order by amount desc) as rank FROM entry ) t WHERE rank <= 3
It is now even simpler: PostgreSQL 9.4 introduced the mode() function: select mode() within group (order by food_id) from munch group by country returns (like user2247323’s example): country | mode ————– GB | 3 US | 1 See documentation here: https://wiki.postgresql.org/wiki/Aggregate_Mode https://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE
Unluckily mysql doesn’t have Analytical Functions. So you have to play with variables. Supposing you have an autoincrement field: mysql> create table mytab ( -> id int not null auto_increment primary key, -> first_column int, -> second_column int -> ) engine = myisam; Query OK, 0 rows affected (0.05 sec) mysql> insert into mytab (first_column,second_column) … Read more
The fastest option depends not only on length of the DataFrame (in this case, around 13M rows) but also on the number of groups. Below are perfplots which compare a number of ways of finding the maximum in each group: If there an only a few (large) groups, using_idxmax may be the fastest option: If … Read more
You can do the counting using a correlated subquery: SELECT b.BookId, a.AuthorId, a.AuthorName, b.Title FROM Author a join Book b on a.AuthorId = b.AuthorId where (select count(*) from book b2 where b2.bookId <= b.BookId and b2.AuthorId = b.AuthorId ) <= 2; For a small database this should be fine. If you create a composite index … Read more
this will give you result that has the minimum price on all records. SELECT * FROM pieces WHERE price = ( SELECT MIN(price) FROM pieces ) SQLFiddle Demo
I’m not really smart from your description, however, the result can be achieved using the following query select your_table.* from your_table join ( select BlilShortName, max(billversion) bmax from your_table group by BlilShortName ) t on your_table.billversion = t.bmax and your_table.BlilShortName = t.BlilShortName From my experience it can be faster in some cases when compared to … Read more