How to limit table results based on number of groups instead of number of rows?

I have a table like:

id         category    subcategory     value0    value1    value2
12wfg2     1           1               100       324       940
12wfg2     1           2               222       404       1000
12wfg2     2           3               333       304       293
12wfg2     2           3               490       490       400
12wfg2     3           2               140       400       499

I want to limit this table to only display results for a sample of any 50 ids. I think there's a way to do this like:

SELECT * FROM (
 SELECT DISTINCT
   my_fields
 , ROW_NUMBER() OVER (ORDER BY id) AS rn
 FROM my_table
)
WHERE rn < 51;

But hoping there's a more efficient method as my dataset is large and this is taking a while.

CodePudding user response：

I can't say if that is more efficient, but another way to do this would be like that:

SELECT * 
FROM your_table
WHERE id IN (SELECT DISTINCT(id) FROM your_table LIMIT 50)

CodePudding user response：

You can consider the below query to limit table results in your query.

Select Distinct your_fields,*,ROW_NUMBER() OVER (ORDER BY id) from
(select * from `your_table`) limit 50