Need help SQL select query

Question

My question is similar to SQL select Group query . But there are changes in the circuit, and I need a different result, as indicated below. the solutions of this link do not give me the correct solutions. You can use SQL script to solve this problem.

Below is my table

Table 1

+--------+----------+---------+ | amount | make | product | +--------+----------+---------+ | 100 | Nokia | Mobiles | | 300 | Samesung | Mobiles | | 700 | Micromax | Mobiles | | 1000 | Karbonn | Mobiles | | 300 | Lava | Mobiles | | 100 | Floyer | Gift | | 500 | Arichies | Gift | | 300 | Feeling | Gift | +--------+----------+---------+ 

Now I want to display the two lowest amounts for each product, and if the quantity is the same as any according to the ascending order of the column alphabet ...

So, I want to build one SQL query that gives me the result, as shown below.

 +--------+----------+---------+ | amount | make | product | +--------+----------+---------+ | 100 | Nokia | Mobiles | | 300 | Lava | Mobiles | | 100 | Floyer | Gift | | 300 | Feeling | Gift | +--------+----------+---------+ 

Please help me build such a query.

Answer 1

This should help you.

The first one had an error, now it is updated.

 SELECT t.* FROM ( SELECT @lim := 2, @cg := '' ) vars, (select * from Table1 order by product,amount, make) t WHERE CASE WHEN @cg <> product THEN @r := @lim ELSE 1 END > 0 AND (@r := @r - 1) >= 0 AND (@cg := product) IS NOT NULL ORDER BY product,amount, make 

Have fun with this and with the violinist: http://sqlfiddle.com/#!2/bdd1a/115/0

Answer 2

Try the following:

 select * from table1 ORDER BY amount DESC LIMIT 2; 

Answer 3

 SELECT amount, make,product FROM (SELECT ROW_NUMBER() OVER (PARTITION BY product ORDER BY amount) AS RowID,* FROM Table1) RESULT WHERE RowID <= 2 

this works well in PostgreSQL, mysql does not support the window function, getting the work of a similar window function in mysql refer