CODEWITHSUNDEEP
sqlsql-servert-sqlgreatest-n-per-group
Leonard
Leonard

Reputation: 458

Join top n records from child table

I am struggling to figure out how to select only the first 4 records from a child table per record in the parent table in a master-detail relationship.

Tables example:

Product         ProductImage
---------       -------------------
Id | Name       PKeyFld1 | PKeyFld2
---------       -------------------
 1 | Apple             1 |        1
 2 | Banana            1 |        2
 3 | Cranberry         1 |        3
                       1 |        4
                       1 |        5
                       2 |        1
                       2 |        2
                       2 |        3
                       3 |        1
                       3 |        3
                       3 |        4
                       3 |        8
                       3 |        9

The primary key for ProductImage is a combination of the two shown fields. I need to get the first 4 images per product, ordered by PKeyFld1, PKeyFld2, which would result in:

ProductImage
-------------------
PKeyFld1 | PKeyFld2
-------------------
       1 |        1
       1 |        2
       1 |        3
       1 |        4
       2 |        1
       2 |        2
       2 |        3
       3 |        1
       3 |        3
       3 |        4
       3 |        8

The nicest solution would be to have only one query with 1 record per product, but I can also deal with two queries; 1 for the products and 1 for the images. In C#, I can fetch them and add the image data to the model before further processing it.

Can somebody help me with the query for the productImages? The hard part is in getting only the top 4 images per product, without limiting the whole ProductImage table to only 4 records. I have done this with Postgres in the past, but cannot find how to do this in SQL Server.

Upvotes: 1

Views: 569

Answers (1)

GMB
GMB

Reputation: 222582

You can use row_number() for solve this greatest-n-per-group problem. As far as concerns, you don't need to involve the Product table.

select PKeyFld1, PKeyFld2
from (
    select t.*, row_number() over(partition by PKeyFld1 order by PKeyFld2) rn 
    from ProductImage t
) t
where rn <= 4
order by PKeyFld1, PKeyFld2

Upvotes: 1

Related Questions