CODEWITHSUNDEEP
sqlsql-server
Eric J.
Eric J.

Reputation: 150158

Query to Return One Row per Person

Given a (simplified) table called Answers like

Id  Person      Answer      Priority
1   Tom         France      Low       
2   Tom         Germany     High      
3   Fred        England     Low       
4   Amy         Italy       High

I would like to write a SQL query that returns one row per person indicating their highest-priority Answer. My thought was to use a self-join

SELECT *
FROM Answers aLow
LEFT OUTER JOIN Answers aHigh 
    ON aLow.Person = aHigh.Person 
       AND aLow.Priority = 'Low' 
       AND aHigh.Priority = 'High'

and then examining in code which Priority columns are non-null, but that returns an extra row for Tom

Id  Person      Answer      Priority    Id      Person  Answer  Priority
1   Tom         France      Low         2       Tom     Germany High      
2   Tom         Germany     High        NULL    NULL    NULL    NULL
3   Fred        England     Low         NULL    NULL    NULL    NULL
4   Amy         Italy       High        NULL    NULL    NULL    NULL

Using this approach the desired output would be 

Id  Person      Answer      Priority    Id      Person  Answer  Priority
1   Tom         France      Low         2       Tom     Germany High      
3   Fred        England     Low         NULL    NULL    NULL    NULL
4   Amy         Italy       High        NULL    NULL    NULL    NULL

I'm sure I must be missing something simple, but cannot put my finger on it.

What am I missing? Is there a better way to approach this problem?

Upvotes: 1

Views: 844

Answers (4)

500 - Internal Server Error
500 - Internal Server Error

Reputation: 28839

if I'm understanding you correctly you should get a desired result from

SELECT *
FROM (SELECT * FROM Answers WHERE Priority = 'Low') aLow
FULL JOIN (SELECT * FROM Answers WHERE Priority = 'High') aHigh 
    ON aLow.Person = aHigh.Person

Upvotes: 0

Ian Preston
Ian Preston

Reputation: 39586

Here's one way of doing it:

with priorityRank as
(
  select *
    , priorityRank = row_number() over (partition by Person
        order by case Priority when 'High' then 1 when 'Low' then 2 end
          , Id)
  from
  Answers
)
select Id
  , Person
  , Answer
  , Priority
from priorityRank
where priorityRank = 1

SQL Fiddle with demo.

Upvotes: 1

Edper
Edper

Reputation: 9322

Have you tried to use DISTINCT like:

SELECT DISTINCT id, Person,Answer, Priority
FROM Answers aLow
LEFT OUTER JOIN Answers aHigh 
ON aLow.Person = aHigh.Person 
   AND aLow.Priority = 'Low' 
   AND aHigh.Priority = 'High'

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460238

You can use a common table expression with the ROW_NUMBER window function:

WITH cte 
     AS (SELECT [id], 
                [person], 
                [answer], 
                [priority], 
                RN = Row_number() 
                       OVER ( 
                         partition BY person 
                         ORDER BY CASE WHEN priority = 'High' THEN 0 ELSE 1 END 
                       ASC) 
         FROM   dbo.answers) 
SELECT [id], 
       [person], 
       [answer], 
       [priority] 
FROM   cte 
WHERE  rn = 1

DEMO

ID  PERSON  ANSWER   PRIORITY
4   Amy     Italy    High
3   Fred    England  Low
2   Tom     Germany  High

Ranking Functions

Upvotes: 1

Related Questions