Tags

Query requirement:
a) 1 agent can list 1 property at a time
b) priority of properties is: HOA, Single house, Condo

PROPERTIES table:

agent     property     type
----------------------------
John      1st ave       'C' 
John      2nd ave       'S'
John      3rd ave       'H'
Paul      4th ave       'S'
Paul      5th ave       'C'
 

select AGENT, 
    MAX (case TYPE
         when 'S' then 1
         when 'H' then 2
         when 'C' then 0
         else -9 end) CODE
  from PROPERTIES
  group by AGENT, TYPE
  order by AGENT, TYPE;
Advertisements