# Selecting multiple rows by given amount of volume

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

• @memo please merge your accounts: dba.stackexchange.com/help/merging-accounts Commented Oct 8, 2017 at 12:09
• It's unclear to me how you are deciding to choose records 1, 2 and 5! Commented Oct 8, 2017 at 13:35
• Does it need to total exactly 1.7500? Or at least 1.7500? You have posed a mathematical problem; please provide the algorithm, then we can help you turn it into SQL. Commented Oct 8, 2017 at 16:31
• you can play with my script using various sample and let me know your problem. Commented Oct 24, 2017 at 10:35
• as a individual question ,it is very fantastics. I see only 2 problem in your explanation.First,what is your real situation like ? Often asking real question or original requirement give you perfect answer.Secondly,there are various combination of arriving at your input,so what is the criteria of shortlisting one of the combination. Commented Oct 24, 2017 at 10:58

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.

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'
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'
HAVING RequiredVolume <= '1.5000'
ORDER BY price DESC, id ASC;

Here's the SQLFiddle to validate that output.

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
)