Search code examples
sqlpostgresqlrow-number

Using ROW_NUMBER () to Compare 1st ARRAY to 2nd, 3rd, 4th, etc

I'm using ROW_NUMBER and I'm trying to compare arr in rn 1 to arr in rn 2,3,4,etc to see if they overlap. I can do this with a subquery / simple join. Is there a way that AVOIDS a join?

rn | id | job | arr    |desired_result
---+----+-----+--------+---------
 1 | 1  | 100 | {1,2}  | {1,2}
 2 | 1  | 101 | {2,3}  | {1,2}
 3 | 1  | 102 | {5,6,8}| {1,2}
 4 | 1  | 103 | {2,7}  | {1,2}

I made a dbfiddle 

--USING JOIN 
WITH a AS (
SELECT 
ROW_NUMBER() OVER (PARTITION BY id ORDER by job) as rn
,*
FROM a_table
)
SELECT *
FROM (
SELECT id,arr
FROM a 
WHERE rn = 1 
) x
JOIN a
ON a.id=x.id
Solution

  • You can use first_value():

    SELECT a.*, first_value(arr) over (partition by id order by job)
FROM a_table a;

    row_number() does not seem necessary.