Calculating Top N items per Group (Without Window Functions)
![Image](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0uwMSDDsZPe62i6Cn6XylatYO0oD3nyHCGOwS3pS4reHUe_0-fHAnc0jhPwqTnBIHvijWremdwj5UFeYRFG7rxzC7mbOn2_YUBcaPqYxTuwOJMkKcHvBSJ29W1ngpsRseywqUnU7Ks-FbdVKs2-xRgv2bQ4t0jPN2BGid_5dCWO9VsL3OdGwkfL3CEQ/s1600/Screenshot%20from%202022-10-15%2012-48-43.png)
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 ; Method 2 (Without Window Function): SELECT t1.group_id,t1.value FROM groups t1 INNER JOIN groups t2 ON t1.group_id = t2.group_id AND t1.value