A pretty common problem is to get the top N items from a group from a SQL database. For example, suppose you wanted to get the last order for each customer - this is a top 1 item grouping by customer ID.
For some database engines, you can use
PARTITION BY to achieve your goal. These functions don’t exist on MySQL and SQLite, so if you are using those engines, then you need to fall back to basic SQL. It is almost certainly not as efficient (basically doing a cross-product), but you can make it work.
Suppose the schema of table
items contains a field for your group (e.g. customer ID) and a unique monotonically increasing field (e.g. the primary key or date)
SELECT i1.category, i1.id FROM items i1 # Join the table with itself JOIN items i2 # We are joining only those that are the same category (e.g. customer ID) AND then for that, do the cross product ON (i1.category = i2.category AND i1.id <= i2.id) # Then create groups based on the ID and the category ID GROUP BY i1.category, i1.id # Finally discard any groups with more than 1 item HAVING COUNT(*) < 2;
Finally, what you really want is the columns, so use that as a sub-query to get the data you really care about.
SELECT * from items WHERE id IN ( SELECT i1.id FROM items i1 LEFT OUTER JOIN items i2 ON (i1.category = i2.category AND i1.id <= i2.id) GROUP BY i1.category, i1.id HAVING COUNT(*) < 2);