Question 371890 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Select pairs with highest amount

I’m trying to select rows, where neither A nor B have an entry with more €:

This is an example of my data

Column A Column B €
A B 100
B A 100
C B 80
B C 80
D E 75
E D 75

And this is the desire output. Because B have a higher value in column € pair up C with B is not possible.

Column A Column B €
A B 100
B A 100
D E 75
E D 75

I tried rank() over (partition by...order by ...) but did not get any results.

The real data ("ok" marks qualifying rows):

Origen   ---   Destino  ---          Dinero     
 2   --------     13    -------      822205 € ok  
13   --------      2    -------      822205 € ok   
32   --------     13    -------      794909 €   
13   --------     32    -------      794909 €   
27   --------     32    -------      774035 € ok   
32   --------     27    -------      774035 € ok   
 2   --------     32    -------      740558 €   
32   --------      2    -------      740558 €   
30   --------     13    -------      717471 €   
13   --------     30    -------      717471 €   
27   --------     13    -------      703739 €   
13   --------     27    -------      703739 €   
17   --------     13    -------      695366 €   
13   --------     17    -------      695366 €   
30   --------     27    -------      694234 €   
27   --------     30    -------      694234 €   
 2   --------     27    -------      686425 €   
27   --------      2    -------      686425 €   
17   --------     30    -------      663328 € ok
30   --------     17    -------      663328 € ok
Tags:

2

Answers


  1. 0

    This only returns rows where no other entry exists that has the same a or b and a higher eur:

    SELECT *
FROM   tbl x
WHERE  NOT EXISTS (
   SELECT FROM tbl y
   WHERE (y.a = x.a OR y.b = x.b)
   AND    y.eur > x.eur
   );

    But for what you seem to need, traverse the table top down (sorted by dinero). Every pair of rows after the first pair potentially depends on previous picks. Not easily implemented with pure SQL (set-based logic). You need a procedural solution.

    Login or Signup to reply.
  2. 0

    The following SQL produces the desired output:

    CREATE TEMPORARY TABLE input_data (
  column_a TEXT,
  column_b TEXT,
  amount INTEGER)
  ON COMMIT DROP;

INSERT INTO
  input_data (column_a, column_b, amount)
VALUES
  ('A', 'B', 100),
  ('B', 'A', 100),
  ('C', 'B', 80),
  ('B', 'C', 80),
  ('D', 'E', 75),
  ('E', 'D', 75);

WITH
  maxes AS (
    SELECT
      column_a,
      column_b,
      amount,
      GREATEST(MAX(amount) OVER (PARTITION BY column_a),
               MAX(amount) OVER (PARTITION BY column_b)) max_member_amount
    FROM
      input_data
  )
SELECT
  column_a,
  column_b,
  amount
FROM
  maxes
WHERE
  amount = max_member_amount;

    The query determines the greater of the maximum amount for each member of a pair and then filters the rows by selecting only those where amount equals the maximum associated with both members.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search