MySQL Query Question

Anvil Q&A
1

I’m having a real writer’s block moment.
I’m even struggling to write the question…

Table A -
id = bigint primary key unique
table_b_id = bigint

Table B -
id = bigint primary key unique auto_increment
name = varchar()

Table A rows can belong to just 1 table B row.

Having chosen 1 or more table B rows, I want to select 1 or more table A rows that belong to each of the chosen table B rows.

I cannot see the wood for the trees :slight_smile:

I appreciate this might just be a ridiculous question, and it might take until the morning for me to see that …

3

I’ve edited the question, though I doubt it helps much :slight_smile:

4

I don’t use SQL much any more, so this is probably wrong…

select A.id 
from A
where A.table_b_id in ( ... )

… but may be somewhere close to the right track.

5

The problem is the limit. I may only want 1 from each, or 10 from each.

TBH, the question is poor and badly thought out. I may edit it in the morning (I tend to blurt out what I’m thinking when I think it :slight_smile:

6

I see. You want a per B-row limit, not a limit on the entire select.

7

I want an A row limit for each selected B row.

8

Here is a link to an article I found that walks you through the “select top n per group” logic

https://www.databasejournal.com/features/mysql/selecting-the-top-n-results-by-group-in-mysql.html

1 Like
9

Thanks for that - and that’s the right wording for the question in “find the top N entries for each category” which I just couldn’t think of last night whilst googling.