Fetch the data based on min(sort

I have a table Product which have many Images.

Product

id name
1  Sofa
2  Bed

Images

id product_id  image_url sort_order
1  1           "url5"    521
2  1           "url1"    200
3  1           "url1"    100
4  2           "url1"    1
5  2           "url2"    2

I want to fetch images where sort_order have minimum value for 100 products like below:

id  product_id  image_url  sort_order
3   1           "url1"     100
4   2           "url1"     1

I know I need to use min(sort_order) for images but don't find the correct syntax. I am trying the below. but no luck

select i.*
from images i
join product p on p.id = i.product_id
where p.id in (1, 2, ....)
  and i.sort_order = min(sort_order)

Any help to build the correct query?

CodePudding user response：

why you need to use min ?? correct me if i am wrong but you said you need images where sort_order = 1, so you just need to change your where and add limit. WHERE i.sort_order = 1 LIMIT 100

CodePudding user response：

Number your images per product and keep the rows numbered 1.

select id, product_id, image_url, sort_order
from
(
  select
    i.*,
    row_number() over (partition by product_id order by sort_order) as rn
  from images i
) numbered
where rn = 1;

An alternative is to query the table twice:

select *
from images
where (product_id, sort_order) in
(
  select product_id, min(sort_order)
  from images
  group by product_id
);

CodePudding user response：

This is pretty bad code, but maybe it'll give you an idea of how you can do this.

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

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ccae44d199f9c9ac0edf4b75dd1b973b

edit: another way to do this is to create a row_number ordered by Sort_Order, so the Row_number = 1 will always be the lowest Sort_order, then we wrap that query in an outer query to apply the where clause

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

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ca31c7650bc87174110bf9aa09b230d9