CODEWITHSUNDEEP
sqlsql-server
jeremy5
jeremy5

Reputation: 39

SQL query for all possible combinations from table

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

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 ;)

Upvotes: 0

Views: 1237

Answers (3)

Stefano Zanini
Stefano Zanini

Reputation: 5916

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

Upvotes: 0

forpas
forpas

Reputation: 164064

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

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

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

Upvotes: 0

Related Questions