find all rows by given amount volume

Yii 2.0 General Discussions
1

I am trying to select rows by given amount volume and order by highest price.

if volume 1.2500 this should select id 2 then 1.(because buyer 2 price higher than 1)

if volume 1.5000 this should select id 2 then 1 finally 3. (when same price like id 1 and 3 lowest id come first)





+--------+------------+-----------+

| id     | volume     | price     |

+--------+------------+-----------+

|      1 | 1.0000     |  14250.00 |

|      2 | 0.2500     |  14251.10 |

|      3 | 0.2500     |  14250.00 |

|      4 | 0.0050     |  14200.00 |

|      5 | 0.5000     |  14200.01 |

+--------+------------+-----------+




$volume= '1.2500';

$q = Buyer::find()

  ->select(['id','volume','price',])

  ->where(['volume' => $volume])

  ->orderBy([

    'price'=>SORT_DESC,

    'id'=>SORT_ASC

  ])->all();

expected result like below volume 1.2500




+--------+------------+-----------+

| id     | volume     | price     |

+--------+------------+-----------+

|      2 | 0.2500     |  14251.10 |

|      1 | 1.0000     |  14250.00 |

+--------+------------+-----------+

expected result like below volume 1.5000




+--------+------------+-----------+

| id     | volume     | price     |

+--------+------------+-----------+

|      2 | 0.2500     |  14251.10 |

|      1 | 1.0000     |  14250.00 |

|      3 | 0.2500     |  14250.00 |

+--------+------------+-----------+
2

Where in your data you have either 1.2500 or 1.5000 volume?

3

above on the first table and volume is table column

4 



+--------+------------+-----------+

| id     | volume     | price     |

+--------+------------+-----------+

|      1 | 1.0000     |  14250.00 |

|      2 | 0.2500     |  14251.10 |

|      3 | 0.2500     |  14250.00 |

|      4 | 0.0050     |  14200.00 |

|      5 | 0.5000     |  14200.01 |

+--------+------------+-----------+

Can you show me where volume either 1.2500 or 1.5000?

5

if total of the volume 1.2500 it will select id 2 and 1 (because sum of volume those 2 rows is 1.2500)ordering by price DESC and id ASC




+--------+------------+-----------+

| id     | volume     | price     |

+--------+------------+-----------+

|      2 | 0.2500     |  14251.10 |

|      1 | 1.0000     |  14250.00 |

+--------+------------+-----------+
6

In this sense… Ok

Next question. Why when you have volume you expected 1,2,3 ids? Why not 1 and 5?

7

because ordering by highest price not by ids.ids in order just when prices are equal

8

mysql or postgres?

9

You can achieve this through having clause or stored expressions. Check this https://stackoverflow.com/questions/14890486/limiting-the-rows-to-where-the-sum-a-column-equals-a-certain-value-in-mysql

10

appreciate for your effort and the link.