SAMPLE 1 For 9473718

0

Please login or register to vote for this query.

(click on this box to dismiss)

Skeptics

Q&A for scientific skepticism

WITH A As (
SELECT 1 as ID, 100 rank , 'Name1' as Name, 1 as val
UNION SELECT 1,    45,  'Name2', 1
UNION SELECT 2,    60,  'Name3', 1
UNION SELECT 2,    42,  'Name4', 1
UNION SELECT 2,    88,  'Name5', 0)
, B AS (
SELECT 1 as ID, 'fn1' as FileName
UNION SELECT 2  , 'fn2')
select 
       B.Id ,
       B.FileName,
       A.Name,
       A.val
FRom B
     INNER JOIN A
     ON a.id = b.id
    INNER JOIN ( 
          select A.Id, MAX(A.Rank)as ExpertRank 
          from A 
          group by A.Id
     ) as NewA 
    ON a.Id = NewA.ID 
       AND a.Rank = NewA.ExpertRank WHERE a.val = 0

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results