Postgres - how to sort the row with the most intersecting array

Question

How do I return a row with the most intersecting rows? Let's say, for instance, I have a table like this:

ID   INTERESTS
---- ------------------
1    {cat,kid,dragon}
2    {orange,apple,kid}
3    {dragon,cat,politics}
4    {php,java,javascript}

Say I wanted to sort the row with the most intersecting array as row 1: It would return something like

ID   INTERESTS              INTERSECTS
---- ---------------------- ------
3    {dragon,cat,politics}  2
2    {orange,apple,kid}     1
4    {php,java,javascript}  0

So far, I haven't seen any code samples that suggest this is possible in Postgres - I don't need the intersects column, but I do need it to be sorted with the most # of intersects.

MatBailie · Accepted Answer · 2022-03-05 03:14:58Z

2

SELECT
  *
FROM
  yourTable   l
CROSS JOIN
  yourTable   r
CROSS JOIN LATERAL
(
  SELECT
    COUNT(*) AS interest_count
  FROM
  (
    SELECT UNNEST(l.interests)
    INTERSECT
    SELECT UNNEST(r.interests)
  )
    AS common_interests
)
  AS i

DEMO : https://dbfiddle.uk/?rdbms=postgres_14&fiddle=47fba6ed42cc22da486b17c68373d531

edited Mar 5, 2022 at 3:14

answered Mar 5, 2022 at 3:04

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

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

Collectives™ on Stack Overflow

Postgres - how to sort the row with the most intersecting array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related