Inner join with first result

  • In SQL Server, there is two tables: Houses, and their images.



    I need a list with 20 houses with the first of their images (only one). I tried:



    SELECT top 20 h.id, h.name, im.id, im.name
    FROM image im
    INNER JOIN house h ON im.house_id = h.id
    WHERE 1=1 AND im.id=(SELECT TOP (1) im2.id FROM image im2 WHERE im.id=im2.id ORDER BY image_code)


    but that runs very slowly. There is any way to improve this query?



    EDIT:



    With the query:



    SELECT   h.id, h.name, im.id, im.name  --  What you want to select
    FROM _house h, _image im -- Tables in join
    WHERE h.id = im.id_house -- The join (equivalent to inner join)
    GROUP BY h.id -- This compresses all entries with the
    -- same h.id into a single row
    HAVING im.id = min(im.id) -- This is how we select across a group
    -- (thus compressing the image table per house)


    I'm getting a error message:



    _image.id' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.



    Then, I change to:



    SELECT   h.id, h.name, im.id, im.name  --  What you want to select
    FROM _house h, _image im -- Tables in join
    WHERE h.id = im.house_id -- The join (equivalent to inner join)
    GROUP BY h.id,im.id, h.name, im.name -- This compresses all entries with the
    -- same h.id into a single row
    HAVING im.id = min(im.id)


    And then I get this result:



    enter image description here



    How can I take out the repeated values?



    EDIT2:



    If somebody want to test the queries, this is the script to create the tables and the data that I'm using now (the real data is about 1Million rows):



    CREATE TABLE _house(
    [id] [int] NOT NULL,
    [name] [varchar](50) NULL
    )


    CREATE TABLE _image(
    [id] [int] NULL,
    [name] [varchar](50) NULL,
    [house_id] [int] NULL
    )

    insert into _house (id, name) values (1,'house1');
    insert into _house (id, name) values (2,'house2');

    insert into _image (id, name, house_id) values (31,'img1',1);
    insert into _image (id, name, house_id) values (32,'img2',2);
    insert into _image (id, name, house_id) values (33,'img3',2);
    insert into _image (id, name, house_id) values (34,'img4',2);

    In SQL Server 2005 or newer version you could use ranking functions") to fetch top N rows per match.

  • Mike Polen

    Mike Polen Correct answer

    10 years ago

    I don't know if there is a faster way, but I would use sub-queries. For example:



    select top 20 h.id, h.name, im.mid, i.name
    from _house h
    join
    (
    select min(id) as mid,house_id from _image
    group by house_id
    ) im on im.house_id=h.id
    join _image i on i.id=im.mid


    Depending on the context it might be faster to generate a temporary table with just one image per house.


License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM