CODEWITHSUNDEEP
mysqlsqldatabase
mahesh
mahesh

Reputation: 4763

SQL select query help required

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..

Upvotes: 6

Views: 235

Answers (3)

Freelancer
Freelancer

Reputation: 9074

Try out this one:

select * from table1 ORDER BY amount DESC LIMIT 2;

Upvotes: 0

solaimuruganv
solaimuruganv

Reputation: 29787

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

Upvotes: 0

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

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

Upvotes: 1

Related Questions