SQL select query help required

Question

My question is similar to SQL select Group query. But there is change in schema and I want different result as mentioned below. the solutions of given link doesn't give me the proper solutions. You can use SQL fiddle to solve this.

Below is my Table

Table1

+--------+----------+---------+  
| 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 amount for each product and if the amount is same then anyone according to ascending alphabet order of make column...

So I want to build single SQL query which gives me result as below..

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

Kindly help me to build such query..

possible duplicate of [mysql: Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group). Take a look at the accepted answer on this with a [link](http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/). — shahkalpesh, Sep 26 '12 at 06:55

score 1 · Accepted Answer · answered Sep 26 '12 at 06:54

1

This should help you ..

First one had a bug, it is updated now.

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 it and with the fiddler : http://sqlfiddle.com/#!2/bdd1a/115/0

answered Sep 26 '12 at 06:54

Dumitrescu Bogdan

7,127
2
23
31

works fine, could u please explain it briefly in q or 2 sentence – mahesh Sep 26 '12 at 06:57
It had a bug, is fine now. Anyway, it's an implementation for row_number() over (partition by .. order by .. of oracle or mssql). A good explanation of how it works you can find here http://explainextended.com/2009/03/05/row-sampling/ – Dumitrescu Bogdan Sep 26 '12 at 06:59

score 0 · Answer 2 · edited Mar 15 '13 at 03:05

0

Try out this one:

select * from table1 ORDER BY amount DESC LIMIT 2;

edited Mar 15 '13 at 03:05

Perception

79,279
19
185
195

answered Sep 26 '12 at 06:48

Freelancer

9,008
7
42
81

Unfortunately this doesn't solve the problem of getting 2 rows per group, it would return 2 rows total – Darkzaelus Sep 26 '12 at 09:06
This will only return 2 group, you mistaken the question completely! – ssilas777 Sep 26 '12 at 09:11

solaimuruganv · Answer 3 · 2012-09-26T07:10:10.200

0

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 doesn't support the window function, by getting work similar windowing function in mysql refer

edited Sep 26 '12 at 07:10

answered Sep 26 '12 at 06:54

solaimuruganv

27,177
1
18
23

gives the error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY product ORDER BY amount) AS RowID,* FROM Table1) RESULT WHERE R' at line 3: SELECT amount, make,product FROM (SELECT ROW_NUMBER() OVER (PARTITION BY product ORDER BY amount) AS RowID,* FROM Table1) RESULT WHERE RowID <= 2 – mahesh Sep 26 '12 at 06:55
yah .. mysql guys should really start implementing the neat stuff on their engines also. – Dumitrescu Bogdan Sep 26 '12 at 07:06

SQL select query help required

3 Answers3

Linked