How to select only distinct result based on particular criteria from tables using join

Question

I have two tables both contains "callid,callnumber,callduration"..now i want to get a call which have same callnumber whereas callduration is greater than a given value.i.e

Select a.callnumber
         , b.callnumber
      from table a
      INNER JOIN table b ON (    a.callnumber = b.callnumber 
                       AND a.callduration-b.callduration > value );

but this returns multiple results fulfilling the creteria whereas i just want a call which have minimum duration difference.

Answer 1

This should help get you on your way.

You need to use a where clause.

 SELECT a.callnumber,
        b.callnumber 
   FROM table a 
  INNER JOIN table b ON a.callnumber=b.callnumber 
   WHERE a.callduration > value AND b.callduration > value. 

Answer 2

the join condition in your original sql and your verbal explanation mismatch, so here are two versions: with the durations above a given value as opposed to their difference being greater than the given value:

v1:

    Select a.callnumber
         , min (a.callduration-b.callduration)  mindiff
      from table a
INNER JOIN table b ON (    a.callnumber = b.callnumber 
                       AND a.callduration > value         
                       AND b.callduration > value        )
  group by a.callnumber
         ; 

v2:

    Select a.callnumber
         , min (a.callduration-b.callduration)  mindiff
      from table a
INNER JOIN table b ON (    a.callnumber = b.callnumber 
                       AND a.callduration-b.callduration > value )
  group by a.callnumber
         ; 

Answer 3

Select 
    a.callnumber, 
    b.callnumber
From table a
    Join table b 
        On a.callnumber = b.callnumber 
            And a.callduration - b.callduration > value And a.rowid < b.rowid

--- use rowid to avoid repetation of data ( I have tested it on Oracle)