There is a table with columns brand, code, name, po1, price, pos2. It is necessary to select from the table rows that have brand + code repeated no more than 2 times. Original table MKT 100 vagon 12 14.3 2 MKT 100 vagon2 13 14.3 22 MKT 100 vagon3 14 14.3 21 MKT 100 vagon4 15 14.3 2 AKT 150 cars 12 14.3 2 KVG 120 tops 12 14.3 2

We must get the MKT 100 vagon 12 14.3 2 MKT 100 vagon2 13 14.3 22 AKT 150 cars 12 14.3 2 KVG 120 tops 12 14.3 2

    2 answers 2

    We enumerate the records within the brand, code, and select the first 2:

     select * from ( select A.*, @n:=if(@g=concat(brand,code),@n+1,1) N, @g:=concat(brand,code) from tableX A, (select @g:='') B order by brand, code ) A where N<=2

    Sorting must begin with the columns participating in the "grouping" for proper counting. And it would not hurt to add any more columns to the sorting, because otherwise there are no guarantees as to which 2 records will be selected.

    • Thanks for the answer. The data was formed, but incorrectly - now only one line is needed, whatever condition I wrote (N <= 5 also gives that N <= 2) - IK
    • @IK First, execute an internal subquery and see how exactly the lines were numbered. You have exactly copied the order in which the variables are assigned in the request, first N should be formed and only after it the current brand-code bundle should remain in @g. And sorting should collect in a row all records with one group. - Mike
    • @IK made a table with your data ... The query works correctly sqlfiddle.com/#!9/27f50f/1 - Mike
    • 'select brand, code, name, pos1, price, pos2 from (select A. *, @n: = if (@ g = concat (brand, code), @ n + 1,1) N, @g: = concat (brand, code) from temp_price A, (select @g: = '') B order by brand, code, price) A where N <= 2 ' - IK
    • @IK What you wrote gives the result you asked for on the data in question. If something is wrong, give an example to sqlfiddle.com and write what is wrong with its output - Mike

    To solve the WRITTEN task, a query will do.

     SELECT * FROM `table` WHERE (`brand`, `code`) IN (SELECT `brand`, `code` FROM `table` GROUP BY `brand`, `code` HAVING COUNT(*) < 3 );

    But the example (or rather, the reference answer) solves another problem - for each pair of brand + code, leave no more than 2 entries. And I really want to ask - and if there are more than two records in a group, do you care which 2 records will be derived from them?