SQL query for all possible combinations from table

Question

I have a table as result of some calculations from SQL database and it looks like this:

[ID] [PAR1] [PAR2]
[A]   [110]  [0.5]
[B]   [105]  [1.5]
[C]   [120]  [2.0]
[D]   [130]  [3.0]
[E]   [115]  [5.5]
[F]   [130]  [6.5]
[G]   [120]  [7.0]
[H]   [110]  [7.5]
[I]   [105]  [8.0]
[J]   [120]  [9.0]
[K]   [110]  [9.5]

It's sorted by PAR2 - less means better result. I need to find the best result of SUM PAR2 from 3 rows, where sum of PAR1 is minimum 350 (at least 350). For ex.:

combination of A+B+C give the the best result of sum PAR2 (0.5+1.5+2.0=4.0), but sum of PAR1: 110+105+120=335 <(350) - condition is not ok, can't use the result,
combination of A+B+D give the result of sum PAR2 (0.5+1.5+3.0=5.0), but sum of PAR1: 110+105+130=345 <(350)- condition is not ok, cant's use the result
combination of A+B+E give the result of sum PAR2 (0.5+1.5+5.5=7.5), but sum of PAR1: 110+105+115=330 <(350)- condition is not ok, cant's use the result
combination of A+B+F give the result of sum PAR2 (0.5+1.5+6.5=8.5), but sum of PAR1: 110+105+130=345 <(350)- condition is not ok, cant's use the result (...)
combination of B+C+D give the result of sum PAR2 (1.5+2.0+3.0=6.5), and sum of PAR1: 105+120+130=355 >(350)- condition is ok!, so we have a winner with best result 6.5

It is an ASP.NET application, so I tried to get the table from database and use VB code behind to get the result, but this is a "manually" work using FOR..NEXT LOOP, takes a time. So it's not nice and good option for calculations like this and also too slow.

I am wondering if there is a better smooth and smart SQL code to get the result directly from SQL Query. Maybe some advanced math functions? Any ideas?

Thanks in advance.

I made some test using forpas solution, and yes, it works very good. But it takes to much time when i added a lot of WHERE conditions, because original table is very large. So I will try to find a solution for using temp tables in function (not procedures). Thank you all for your answers.

forpas, special thanks also for example and explanation, in this way you let me quikly understand your idea - this is master level ;)

what type of SQL database? SQL Server? Oracle? MySQL? Something else? — ADyson
– ADyson, Commented May 13, 2019 at 14:42
Thanks. I added a suitable tag. You need to do that always when asking SQL questions, because otherwise people cannot suggest a solution, in case it involves functions or keywords specific to your version of SQL. Not all database engines are created equal... — ADyson
– ADyson, Commented May 13, 2019 at 14:50

forpas · Accepted Answer · 2019-05-13 15:18:25Z

1

You can use a double inner self-join like this:

select top 1 * from tablename t1
inner join tablename t2 on t2.id > t1.id
inner join tablename t3 on t3.id > t2.id
where t1.par1 + t2.par1 + t3.par1 >= 350
order by t1.par2 + t2.par2 + t3.par2

See the demo.
Results:

> ID | PAR1 | PAR2 | ID | PAR1 | PAR2 | ID | PAR1 | PAR2
> :- | ---: | :--- | :- | ---: | :--- | :- | ---: | :---
> A  |  110 | 0.5  | C  |  120 | 2.0  | D  |  130 | 3.0

So the winner is A+C+D because:

110 + 120 + 130 = 360 >= 350

and the sum of PAR2 is

0.5 + 2.0 + 3.0 = 5.5

which is the minimum

answered May 13, 2019 at 15:18

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jeremy5 Over a year ago

forpas, thank you very much! It seems a very smart solution. Let me do some test in databse, and I will get back to you. And yes, 5.5 is the best result in this case, I will also try on another tables.

forpas Over a year ago

@jeremy5 did you accept an answer with cross joins which is less efficient than straight joins?

jeremy5 Over a year ago

Yes, by mistake, I thought that it's possible to mark two or more also. Your solution is the best option and you helped me a lot, thanks ;) Should I put next question into new topic about tempTable or continue discussion here?

forpas Over a year ago

If it involves new requirement then it must be a new question

mkRabbani · Accepted Answer · 2019-05-13 15:12:50Z

Check this. I feel its accurate or close to your requiremnt-

WITH CTE (ID,PAR1,PAR2)
AS
(
    SELECT 'A',110,0.5  UNION ALL
    SELECT 'B',105,1.5  UNION ALL
    SELECT 'C',120,2.0  UNION ALL
    SELECT 'D',130,3.0  UNION ALL
    SELECT 'E',115,5.5  UNION ALL
    SELECT 'F',130,6.5  UNION ALL
    SELECT 'G',120,7.0  UNION ALL
    SELECT 'H',110,7.5  UNION ALL
    SELECT 'I',105,8.0  UNION ALL
    SELECT 'J',120,9.0  UNION ALL
    SELECT 'K',110,9.5
)
SELECT B.AID,B.BID,B.CID,SUM_P2,SUM_P1
(
    SELECT * , ROW_NUMBER() OVER (PARTITION BY CHAR_SUM ORDER BY CHAR_SUM) CS
    FROM 
    (
        SELECT ASCII(A.ID) + ASCII(B.ID)+ASCII(C.ID) CHAR_SUM,
        A.ID AID,B.ID BID,C.ID CID,
        (A.PAR2+B.PAR2+C.PAR2) AS SUM_P2,
        (A.PAR1+B.PAR1+C.PAR1) AS SUM_P1
        FROM CTE A
        CROSS APPLY CTE B
        CROSS APPLY CTE C
        WHERE A.ID <> B.ID AND A.ID <> C.ID AND B.ID <> C.ID
        AND (A.PAR1+B.PAR1+C.PAR1) >= 350
    ) A
)B
WHERE CS = 1

Stefano Zanini · Accepted Answer · 2019-05-13 16:09:16Z

0

You might try to cross join the table with itself three times. This way you would have all the combination of three rows pivoted on a single row, thus making you able to apply the conditions required and picking the maximum value.

select  t1.ID, t2.ID, t3.ID, t1.PAR2 + t2.PAR2 + t3.PAR2
from    yourTable t1
cross join
        yourTable t2
cross join
        yourTable t3
where   t1.ID < t2.ID and t2.ID < t3.ID and
        t1.PAR1 + t2.PAR1 + t3.PAR1 >= 350
order by t1.PAR2 + t2.PAR2 + t3.PAR2 ASC

While this solution should technically work, cross joining tables is not ideal performance-wise, even more when doing it multiple times. If the size of the table is going to grow over time, and you have the option to apply the calculation at code level, I think it would be advisable to do so.

Edit

Changed the where clause including Serg's suggestion

edited May 13, 2019 at 16:09

answered May 13, 2019 at 15:04

Stefano Zanini

5,9362 gold badges16 silver badges33 bronze badges

2 Comments

Serg Over a year ago

Probably where t1.ID < t2.ID and t2.ID < t3.ID to exclude permutations if they are considered the same solution.

jeremy5 Over a year ago

Thanks for your answer. This works also, in similar way like forpas proposition.

Collectives™ on Stack Overflow

SQL query for all possible combinations from table

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related