Selecting multiple rows by given amount of volume

Question

i have a table like this

+--------+------------+-----------+
| 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     |  14255.01 |
+--------+------------+-----------+

Simply if volume 1.7500, how to select like bellow by ordering(highest price)

+--------+------------+-----------+
| id     | volume     | price     |
+--------+------------+-----------+
|      5 | 0.5000     |  14255.01 |
|      2 | 0.2500     |  14251.10 |
|      1 | 1.0000     |  14250.00 |
+--------+------------+-----------+

And if volume 1.5000 it also select the least amounts of records needed to calculate to the input volume

+--------+------------+-----------+
| id     | volume     | price     |
+--------+------------+-----------+
|      5 | 0.5000     |  14255.01 |
|      1 | 1.0000     |  14250.00 |
+--------+------------+-----------+

some guys say i cant do this with sql

Answer 1

You would need to use a reduction method to slowly calculate the parts that make up the target volume. For instance 1.75, you would as you say be 1.00, then 0.5 then 0.25. You can use a loop to calculate the remainder using modulus, then use that remainder to find the next largest volume less than or equal to your value.

This is how I would do it (there might be better ways but this works)

CREATE TABLE VolumePrice (
    id int not null,
    volume decimal(9,4) not null,
    price decimal(9,2) not null
);

insert VolumePrice (id, volume, price)
values (1, 1.0000, 14250.00),
       (2,0.2500,14251.10),
       (3,0.2500,14250.00),
       (4,0.0050,14200.00),
       (5,0.5000,14255.01);

-- create a temp table to hold the output
create table output (
    id int auto_increment not null primary key, 
    remainder decimal(9,2), 
    volume decimal(9,2)
);

DELIMITER //

create procedure do_insert()
begin
set @v = 1.75;
set @remainder = @v;

while @remainder > 0
do
    insert output(remainder, volume)
    select @remainder % max(volume)
         , max(volume) as volume
      from volumeprice
     where volume <= @remainder;

  set @remainder = (select remainder 
                      from output 
                  order by id desc
                     limit 1);
end while;
end;
//

DELIMITER ;    

CALL do_insert();

select v.*
  from output as o
  join (select volume, max(price) as max_price
          from volumeprice
      group by volume) as vp on vp.volume = o.volume
  -- I only link back to the volumeprice table here to obtain the id, 
  -- if you do't need the id you can remove this part and just select vp.* instead...
  join volumeprice as v 
    on v.volume = vp.volume 
   and v.price = vp.max_price;

And here is a SQL Fiddle that seems to do the trick.

Answer 2

It looks like you have two questions.

The first:

Simply if volume 1.7500, how to select like bellow by ordering(highest price)

Here's a solution that should provide that result set:

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE (id <> t.id AND price > t.price) OR (id < t.id AND price = t.price) OR (id = t.id)) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.7500'
ORDER BY price DESC, id ASC;

Here's the SQLFiddle to validate the output.

As for the second question, it looks like an entirely different problem:

And if volume 1.5000 it also select the least amounts of records needed to calculate to the input volume

I basically order by volume instead of price which should then get what you want:

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE volume > t.volume OR (id < t.id AND volume = t.volume) OR (id = t.id)) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.5000'
ORDER BY price DESC, id ASC;

Here's the SQLFiddle to validate that output.

Answer 3

Same problem can be written solve in PURE SQL without RBAR or REcursive

This script is in Sql server 2005+,i think it can be very easily converted to mySQL.

Let me know the LIMITATION

First create number table.

SELECT TOP (1048576)
    n = ISNULL(CONVERT(integer, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 0)
INTO dbo.Numbers
FROM sys.columns AS c
CROSS JOIN sys.columns AS c2
CROSS JOIN sys.columns AS c3;

CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.Numbers (n)
WITH (MAXDOP = 1, SORT_IN_TEMPDB = ON);

Then final script,

declare @input decimal(9,4)=1.7500 
if object_id('tempdb..#VolumePrice') is not null 
drop table #VolumePrice

create  table #VolumePrice (
    id tinyint  PRIMARY KEY NONCLUSTERED,
    volume decimal(9,4) not null,
    price decimal(9,2) not null,
     bit_value   AS 
                CONVERT
                (
                    integer, 
                    POWER(2, id - 1)
                )
                PERSISTED UNIQUE CLUSTERED
);

insert #VolumePrice (id, volume, price)
values (1, 1.0000, 14250.00),(2,0.2500,14251.10),
      (3,0.2500,14250.00),(4,0.0050,14200.00),(5,0.5000,14255.01);


DECLARE 
    @max integer = 
    POWER(2,
        (
            SELECT COUNT(*) 
            FROM #VolumePrice AS s
        )
    ) - 1;

;

WITH CTE
AS (
    SELECT n.n
        ,c.id
        ,c.volume
    FROM dbo.Numbers AS N
    CROSS APPLY (
        SELECT s.id
            ,s.volume
        FROM #VolumePrice AS s
        WHERE n.n & s.bit_value = s.bit_value
        ) c
    WHERE N.n BETWEEN 1
            AND @max
    )
    ,cte1
AS (
    SELECT n
        ,sum(volume) vol
        ,count(n) HavingLeastcount
    FROM cte
    GROUP BY n
    HAVING sum(volume) = @input
    )
SELECT *
FROM cte c
WHERE EXISTS (
        SELECT n
        FROM cte1 c1
        WHERE n = c.n
        )