Search code examples
mysqlcountlimit

MYSQL I need to first sort by a column and then limit the results based on a number of times a value appears in another column/columns

First, I am an extreme noob when it comes to posting on here, and with MYSQL, so I apologize for any format or syntax errors.

Take the below table.

name1 name2 widgets
Johnny Suzie 3
Johnny Tommy 5
Anna Suzie 2
Johnny Billy 7
Anna Billy 9

I need to sort by "widgets" first, and then limit results to where a name appears a maximum of 2 times in the name1 column, and a maximum of 1 time in the name2 column. So the result would be

name1 name2 widgets
Anna Billy 9
Johnny Tommy 5
Johnny Suzie 3

I've found several articles on here about getting counts but they don't seem to meet my specific needs. They all just give me a calculated total. I would assume I need to use some combination of the COUNT and LIMIT functions but I can't quite figure out where to start. If any more info is needed please let me know. Any help would be greatly appreciated.

Solution

  • you can use a common table expression CTE to assign a row_number to the name1 and name2 columns. Then select from the CTE and use where to only select rows where the row_number is <= 2 or = 1.

    with t1 as (
SELECT name1, name2, widgets, 
      row_number( ) over( partition by name1 order by widgets ) name1_row_num
FROM widgetTable 
    )
select name1, name2, widgets
from   t1  
where   name1_row_num <= 2 
order by widgets