cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Retrieve certain number of rows per category

Hello all!

I have a data source that's a MySQL DB. Within that is a huge table (+10million rows) which I don't wish to refresh in its entirety everytime.

Keeping the first rows is simple, but how do I keep the first rows for each distinct category in the table?

 

In my case the data is identified by customer name, so how would I got about writing a query to retrieve the first 10,000 rows for each customer?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Retrieve certain number of rows per category

@Anonymous,

Do you want to write specific query to get data when importing data from MySQL database into Power BI Desktop? If so, the blogs below describe that how to  select first N rows for each category using MySQL query. You can refer to them.

http://www.sqlines.com/mysql/how-to/get_top_n_each_group
http://samalpramod.blogspot.com/2014/12/getting-top-n-rows-by-each-category-in.html


Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Moderator v-yuezhe-msft
Moderator

Re: Retrieve certain number of rows per category

@Anonymous,

Do you want to write specific query to get data when importing data from MySQL database into Power BI Desktop? If so, the blogs below describe that how to  select first N rows for each category using MySQL query. You can refer to them.

http://www.sqlines.com/mysql/how-to/get_top_n_each_group
http://samalpramod.blogspot.com/2014/12/getting-top-n-rows-by-each-category-in.html


Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Retrieve certain number of rows per category

That helped thanks.

 

I now have a new problem, which is that some of the columns are titled with a dot in their name

 

e.g. customer.name

 

The query refuses to accept these, square brackets and backticks don't help either. Any ideas?

Moderator v-yuezhe-msft
Moderator

Re: Retrieve certain number of rows per category

@Anonymous,

Please open a new thread about the new issue.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Retrieve certain number of rows per category

Got it!

 

Single quotes ' around a column name with a dot will allow the query to be accepted.

 

For reference, if you have a DB with an invalid character (in the From part), then you need to encase it in backticks