MSSQL – Random i gropu by koristeći cross apply

Problem :

imamo 10 apartmana , za svaki apartman imamo po 5 slika… potrebno je selektirati po jednu random sliku za svaki od apartmana. S obzirom da group by traži agregatnu funkciju kod selekcije, korištenje Min(image_url) kod selecta će uvijek vraćati istu sliku, onu koja je Min.

 

Rješenje:


select
 MIN(A.id) AS slika_id,
 A.obj_id,
 dtbl.image_url
from
 tbl_slike A
 cross apply
 (select TOP 1 image_url, obj_id
 from tbl_slike B
 WHERE B.obj_id = A.obj_id
 order by newid()
 ) dtbl
group by A.obj_id, dtbl.image_url
This entry was posted on Wednesday, August 10th, 2016 at 5:52 pmand is filed under MSSQL Database Development. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.

Comments are closed.