How do I best get the top 2 unique rows when a JOIN is involved?

Question

I have this query:

select top(2)
    property_id_ref
    ,image_file
    ,property_name 
from property_master a 
inner join image_master b 
    on a.property_id=b.property_id_ref 
inner join customer_master c 
    on a.customer_id=c.customer_id

When I execute it, I get the following result:

512 ~/propertyimg/3954493 id_1.jpg Commercial Land 
512 ~/propertyimg/3954493.jpg Commercial Land

But I need the output distinct property_id_ref with random image_file from the property_id_ref like this:

512 ~/propertyimg/3954493 id_1.jpg Commercial Land 
513 ~/propertyimg/3119918 Id.jpg Residential Plot

For that I made a query like:

select top(2) 
    max(pm.property_name) as property_name
    ,max(im.property_id_ref) as property_id_ref
    ,CONVERT(varchar(5000),  max( CONVERT(binary, im.image_file))) as image_file 
from property_master pm
inner join image_master im
    on pm.property_id=im.property_id_ref 
inner join customer_master cm
    on pm.customer_id=cm.customer_id 
group by im.property_id_ref

So I got the same output as the one I expected. I want to know whether this is the right way to do it, or is there any other better way of doing the same thing?

I am using SQL Server 2005.

Answer 1

The way you do is the right one. An group by of the ID_Ref and a random member by max. It's completly OK and I see no reason why to change it.

Answer 2

If you really only have that query you posted in example, this will work fine:

SELECT TOP (2)
    pm.property_id,
    pm.property_name,
    (SELECT TOP 1 image_file
     FROM image_master
     WHERE property_id_ref = pm.property_id) AS image_file
FROM
    property_master pm
-- This is only needed if it's possible that [image_file] can be NULL and you
-- don't want to get those rows.
WHERE
    EXISTS (SELECT * FROM image_master
            WHERE property_id_ref = pm.property_id)

I assume your query is more complex than that though, but I can't give you a more specific query unless you post your real query.