These kinds of questions are more common than you think: Out of a list of Twitter users, find their N recent tweets. Out of all the cities, find the top N cities in each country by population. Out of all orders on the e-commerce website, find the top N recent orders for each customer. Solving these questions becomes pretty easy if we use a window function like row_number(), but solving this without any window function makes the task challenging. Here we will see both approaches. Question: Display the top 3 recent values from every group Table : groups Required Output: Method 1 (Using Window Function): WITH ranked AS ( SELECT group_id,value, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY value DESC ) AS rn FROM groups ) SELECT group_id,value FROM ranked WHERE rn <= 3 ; ...
Comments
Post a Comment