SELECT with conditions for preferential source

Question

I have a query which I've been using for sometime however I have a new scenario which I'm not sure how to handle. The query below extracts avg pricing from a table called availables. The problem I have now is that, the data which is contained in this table can come from one of two sources. So I added a source field to the table.

The problem is one or both of these sources for any record may be blank which is fine now, or they may contain data for the same record (which is my problem). The records are normally unique except for the new alternative sources.

IE

Room Date        Price   Source
27   2010-02-28  $27.99  1
27   2010-02-28  $25.99  2

I this one instance I need the query to pull only the first source and ignore the second, but only if they both exist.

SELECT 
    rooms.id, 
    name, 
    ppl, 
    private AS exclusive, 
    MIN(spots) AS spots, 
    AVG(availables.price) AS price FROM "rooms" 
INNER JOIN 
    "availables" ON availables.room_id = rooms.id 
WHERE 
    (("rooms".hostel_id = 6933) AND 
     (rooms.active IS true AND bookdate BETWEEN '2011-02-20' AND '2011-02-22')) 
GROUP BY 
    rooms.id, name, ppl, private ORDER BY price

Answer 1

add a subquery like:

inner join
  (select Room, Date, min(Source) as Source
  from availables
  group by Room, Date) first_available
on first_available.Room = available.Room
    and first_available.Date = available.Date
    and first_available.Source = available.Source

Answer 2

SELECT  *
FROM    rooms
JOIN    (
        SELECT  DISTINCT ON (room_id, date) *
        FROM    availables
        ORDER BY
                room_id, date, source
        ) a
ON      a.room_id = rooms.id
WHERE   …