Select rows where count() = n

I'm implementing a search functionality where the results should show results page and for each result, the main image and up to 3 more thumbnails.

Right now in the procution version, for each ad it makes 1 select to return the images from the database which it terrible for performance, so I've changed it to a single query that does basically the following:

select * from AdImages order by IsMainImage desc, AdImageId

and returns something like:

AdImageId   AdId        IsMainImage FilePath
----------- ----------- ----------- ----------------------------------------
1           1           1           9c513f10-5480-4e41-89c6-074b36051999.jpg
5           2           1           f64f9c12-398e-445f-9724-baebe40930b1.jpg
6           4           1           8187d566-b296-4ab0-85e5-b9fc86f293b7.jpg
8           5           1           b8165008-09b3-4258-bf54-043195138344.jpg
10          6           1           86c636ed-f4ed-4f7e-8c7e-fc0b24faa956.jpg
11          7           1           4409a3fd-2bc0-4512-9850-6f5146193e50.jpg
13          8           1           b9b66c48-92b7-479a-a85d-dc6d26b03ebc.jpg
14          9           1           9f3f06ad-4fe1-43a5-8cce-3bb804bb10b7.jpg
16          10          1           016c30dc-5ee8-40d8-9d0f-398f444d7a7b.jpg
19          11          1           e5e56602-1af7-492b-8a8e-b61ac86b751b.jpg
2           1           0           02d44ce1-0de6-4e22-b4ef-043a72e9b5e8.jpg
3           1           0           8c4e19db-faff-44c2-9aab-6a96ab2a8e22.jpg
4           1           0           d8c2464a-277c-40fa-ab43-d2455e819e7e.jpg
7           4           0           d1430ae0-df51-43b7-acea-50d606eee5ba.jpg
9           5           0           b947ae4c-653d-4c27-9edd-567d977e1af3.jpg
12          7           0           3080c947-3769-4762-bb29-f1f9c5303ecd.jpg
15          9           0           d2543ce3-1e65-4a18-80d6-584de0025f1a.jpg
17          10          0           03b26d6a-4e0c-4393-9b5a-d9f2a24d36da.jpg
18          10          0           cde5dacd-3984-4cea-b56f-c3a6c5b82fa0.jpg
20          11          0           9e286ac0-25b1-4a05-af83-26e5d0002c2a.jpg
21          11          0           b1266770-9926-462c-8ec0-e965b21021eb.jpg
22          11          0           0542bd2a-4c4b-41d4-b51b-d311f42f0da9.jpg
23          11          0           b1cc44c9-50c4-4e81-bc9a-a0a4b515e709.jpg

My local db is very small but I could notice a very good performance gain, anyway, I think it could be better if I could make this query return only up to 4 rows for each ad instead of all the rows for each ad as it is doing. But to do so, it should be something like where count(AdId) == 4 which I'm not sure is possible.

I'm also using Entity Framework here. Any extra advice would be very welcome.

Answers


Use Window Function

select AdImageId ,AdId ,IsMainImage ,FilePath 
from(
select row_number() over(partition by Adid order by IsMainImage desc, AdImageId) rn,* 
from AdImages)a
where rn<=4

If I am understanding you correctly, you can just return the TOP xx results.

SELECT TOP(3) * from AdImages order by IsMainImage desc, AdImageId;

This will return only the top 3 results.


Need Your Help

iOS - Global exception handler

iphone ios exception-handling crash crash-reports

Is there anyway to implement a global exception handler for iPhone apps such that exceptions, instead of silently crashing the app, could allow for some sort of message?