How to create a request that will display the first two lines of sports, it, etc. enter image description here

The conclusion specifically for this table on the screen should be as follows:

enter image description here

  • The query was the following: "SELECT * FROM news WHERE news_category = 'sports' OR news_category = 'it' OR news_category = 'politics'" . How to add LIMIT 2? That lines with the values ​​of sports, it, politics was not more than 2? - Alex Kalinchuk
  • And what is the "first" line? in SQL there is no concept of the order of lines, until the sorting in which they are “first” is indicated - Mike
  • Possible duplicate question: Limit the number of records in the group when grouped in MySQL - Mike
  • @Mike but there is an order for iterating over rows in a table. I need to output 2 lines of the specified values. As I understand it, those that start the table will be displayed - Alex Kalinchuk
  • brute force does not exist. or rather, the database usually takes in the order in which they are on the disk, but it is impossible to predict which order will be on the disk. The DBMS has the right to return the lines in any order, different from the previous one, at each reference! Of course, you can not use order by, but then we can talk not about the “first two” lines, but about “two random” - Mike

1 answer 1

Suppose your table: id || text || news_category

Then:

select id, `news_category`, text from ( select id, `news_category`, text , (@num:=if(@group = `news_category`, @num +1, if(@group := `news_category`, 1, 1))) row_number from table1 t CROSS JOIN (select @num:=0, @group:=null) c order by `news_category`, id desc, text ) as x where x.row_number <= 2

Reply based on https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results