SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE images
    (id int , product_id int, image_url varchar(255), sort_order varchar(10))
;
    
INSERT INTO images
    (id, product_id, image_url, sort_order)
VALUES
    (1, 1, 'url5', 5),
    (2, 1, 'url1', 2),
    (3, 1, 'url1', 1),
    (4, 2, 'url1', 1),
    (5, 2, 'url2', 2)
;


CREATE TABLE product
    ([id] int, [name] varchar(4))
;
    
INSERT INTO product
    ([id], [name])
VALUES
    (1, 'Sofa'),
    (2, 'Bed')
;
7 rows affected

SELECT p.id
	,p.name
	,*
FROM product p
INNER JOIN (
	SELECT i.product_id
		,min(sort_order) AS minSortOrder
	FROM images i
	GROUP BY i.product_id
	) i ON i.product_id = p.id
INNER JOIN images ii ON ii.product_id = p.id
	AND ii.sort_order = i.minSortOrder


id
name
id
name
product_id
minSortOrder
id
product_id
image_url
sort_order


1
Sofa
1
Sofa
1
1
3
1
url1
1

2
Bed
2
Bed
2
1
4
2
url1
1


select * from (
SELECT p.id,p.name,ii.image_url,ii.sort_order
	,row_number() over (partition by ii.product_id order by sort_order) as RowNumber
FROM product p
INNER JOIN images ii ON ii.product_id = p.id

) s
where s.rownumber = 1


id
name
image_url
sort_order
RowNumber


1
Sofa
url1
1
1

2
Bed
url1
1
1