Search code examples
sqlpostgresqlinner-join

Select closest match

I am trying to merge two tables (pc with mc), but the statement below could yield multiple records from mc. I'd like the query to return only the match in mc where cast(pc.ten as date) - cast(mc.to as date) is positive but as small as possible. How do I do that? 

CREATE TABLE test AS SELECT DISTINCT pc.number, mc.number
FROM pc
INNER JOIN mc
ON pc.member = mc.member AND pc.ned = mc.ned AND cast(pc.ten as date) BETWEEN cast(mc.to as date) + 1 AND cast(mc.to as date) + 11 AND
pc.ned IS NOT NULL AND mc.ned IS NOT NULL;
Solution 
  • CREATE OR REPLACE VIEW test AS 

  SELECT n1, n2 
  FROM (
    SELECT 
      pc.number as n1, 
      mc.number as n2, 
      ROW_NUMBER() OVER (ORDER BY cast(pc.ten as date) - cast(mc.to as date) ASC) AS Rank
    FROM pc
    INNER JOIN mc ON pc.member = mc.member AND pc.ned = mc.ned 
    WHERE cast(pc.ten as date) - cast(mc.to as date) > 0
  ) 
  WHERE Rank = 1