Get top n records for each group of grouped results

Need help Database
1

This stack overflow example shows exactly what I want to do in Bubble. mysql - Get top n records for each group of grouped results - Stack Overflow

Given a table like you’ll see in the link… with person, group, and age columns, how would you get the 2 oldest people in each group? (Ties within groups should not yield more results, but give the first 2 in alphabetical order)

In my Bubble application, I want to return the first 3 things per Creator when sorting by Create Date.

Is this possible? I’ve been racking my brain for days. Thanks!

3

Well, in vanilla Bubble you’d structure your database such that you can (1) get all persons in a group and (2) sort that list by age descending. The two oldest persons in that list would be the first two items.

4

That’s easy, but not what I’m looking for. In this example I’d want the resulting list to show the first two people in EACH group.

I’m MySQL this would involve a GROUP and a LIMIT. I’m struggling to see a way to do that here.

5

Well if you can get the top two persons in each group, the merge of all those lists is all your persons.

But yeah I’d probably just code this but you’re not me.

6

If the total amount of groups is variable and changes with time, a merge wouldn’t work.

Is there a plugin that let’s you write SQL queries against your own bubble DB? Haven’t come across anything myself that could accomplish this.

I think I’m gonna have to rework the feature otherwise.

7

No (Bubble queries are Bubble queries and do what Bubble allows you to do), but surely you can accomplish this.

OK, so if you wanted to display this, here’s what you’d do in Bubble:

Have a repeating group fed by Creators. Nest a repeating group in the outside repeating group’s cell. The nested RG source is “this cell’s Creator’s Things, sorted descending by created date” (or similar, depending on how your database is set up). Then only display three of them (either by limiting what the nested RG displays, or by :items until 3).

1 Like
8

Very interesting! Now, not to get greedy, but would there be a way to stitch this back together into a single list, in a somewhat efficient way, so that I can randomize?