CODEWITHSUNDEEP
sqlsqlite
Steven Choi
Steven Choi

Reputation: 25

Finding the Max of multiple elements in the same column

Table A is given with A.i, A.j, and A.val. A makes a 3x3 matrix.

I need to find the Max(A.val) from A.val where its ij, i+1j, ij+1,i-1j, ij-1 if it exists. However, I can't find any solution to finding the max value of specific elements of the same column. Please help.

Sample:

i   |   j   |   val
0       0       7
0       1       5
0       2       8
1       0       10
1       1       7
1       2       7
2       0       2
2       1       0
2       2       5

answer output:

i | j | val

0       0       10
0       1       8
0       2       8
1       0       10
1       1       10
1       2       8
2       0       10
2       1       7
2       2       7

Upvotes: 1

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The only sensible interpretation that I can think of is that you want the maximum of the four adjacent cells plus the current cell. Let me assume that your database supports greatest(), because that simplifies the problem:

select t.*,
       greatest(val,
                lag(val, 1, val) over (order by i),
                lead(val, 1, val) over (order by i),
                lag(val, 1, val) over (order by j),
                lead(val, 1, val) over (order by j)
               ) as neighborly_maximum                
from t;

You can also do this with left join:

select t.*,
           greatest(val,
                    coalesce(tup.val, val),
                    coalesce(tdown.val, val),
                    coalesce(tleft.val, val),
                    coalesce(tright.val, val)
                   ) as neighborly_maximum                       
from t left join
     t tup
     on tup.i = t.i and tup.j = t.j + 1 left join
     t tdown
     on tdown.i = t.i and tdown.j = t.j - 1 left join
     t tleft
     on tleft.i = t.i - 1 and tleft.j = t.j left join
     t tright
     on tright.i = t.i + 1 and tright.j = t.j;

Upvotes: 1

GMB
GMB

Reputation: 222432

With SQLite >= 3.25 (2018), you can use window functions. LAG() and LEAD() let you access the neighbour records. Then, core function MAX() can be used to compute the maximum of a list of value.

SELECT
    i,
    j,
    MAX(
        val,
        COALESCE(LEAD(val) OVER(PARTITION BY j ORDER BY i), 0), -- i+1 / j
        COALESCE(LAG(val)  OVER(PARTITION BY j ORDER BY i), 0), -- i-1 / j
        COALESCE(LEAD(val) OVER(PARTITION BY i ORDER BY j), 0), -- i   / j+1
        COALESCE(LAG(val)  OVER(PARTITION BY i ORDER BY j), 0)  -- i   / j-1
    ) res
FROM mytable

This DB fiddle demo on SQLite 3.26 with your sample data returns :

| i   | j   | res |
| --- | --- | --- |
| 0   | 0   | 10  |
| 0   | 1   | 8   |
| 0   | 2   | 8   |
| 1   | 0   | 10  |
| 1   | 1   | 10  |
| 1   | 2   | 8   |
| 2   | 0   | 10  |
| 2   | 1   | 7   |
| 2   | 2   | 7   |

In earlier versions of SQLite, one solution would be to make 4 joins, as follows :

SELECT
    t.i,
    t.j,
    MAX(
        t.val, 
        COALESCE(t1.val, 0), 
        COALESCE(t2.val, 0), 
        COALESCE(t3.val, 0), 
        COALESCE(t4.val, 0)
    ) res
FROM mytable t
LEFT JOIN mytable t1 ON t.i = t1.i + 1 AND t.j = t1.j 
LEFT JOIN mytable t2 ON t.i = t2.i - 1 AND t.j = t2.j 
LEFT JOIN mytable t3 ON t.i = t3.i     AND t.j = t3.j + 1
LEFT JOIN mytable t4 ON t.i = t4.i     AND t.j = t4.j - 1

Demo on db fiddle

Upvotes: 0

Related Questions