Consider the following set, which is the content of the table variable @ProductContent that was just created for the query that runs against it:
Notice how items are sorted by media_type_id, image_weight, image_id and finally image_width DESC. If I run the following query:
SELECT -- <images>
pcs.image_type AS [@Type]
,pcs.image_type_id AS [@ImageTypeId]
,(
SELECT -- <imageCollection>
pcc.image_id AS [@Guid]
,pcc.image_angle AS [@Angle]
,pcc.image_weight AS [@Weight]
,(
SELECT -- <image>
pci.mime_type AS [@MimeType]
,pci.content_guid AS [@Guid]
,pci.url AS [@Url]
,pci.media_type_id AS '@MediaTypeId'
,t.media_type_description '@Description'
,(
SELECT DISTINCT -- <attribute>
a.meta_attribute_id '@Id'
,a.meta_attribute_name '@Name'
,v.meta_value_name AS '@Value'
FROM [ADS2].dbo.ads_digital_content_meta m
JOIN [ADS2].dbo.ads_digital_content_meta_atr_voc a ON a.meta_attribute_id = m.meta_attribute_id
JOIN [ADS2].dbo.ads_digital_content_meta_value_voc v ON v.meta_value_id = m.meta_value_id
WHERE m.content_guid = pci.content_guid
FOR XML PATH('Attribute'), TYPE)
FROM
@ProductContent pci
JOIN [ADS2].dbo.ads_digital_content_media_type t ON t.media_type_id = pci.media_type_id
WHERE
pci.image_id = pcc.image_id
GROUP BY pci.content_guid, pci.mime_type, pci.url, pci.media_type_id, pci.image_width, t.media_type_description
FOR XML PATH('Image'), TYPE)
FROM @ProductContent pcc
WHERE pcc.image_type_id = pcs.image_type_id
AND pcc.product_id = pc.product_id
GROUP BY
pcc.product_id
,pcc.image_weight
,pcc.image_id
,pcc.image_angle
FOR XML PATH('ImageCollection'), TYPE)
FROM @ProductContent pcs
WHERE pcs.media_type_id = 15
AND pcs.product_id = pc.product_id
GROUP BY
pcs.image_type,
pcs.image_type_id
FOR XML PATH('Images'), TYPE
For some reason the the images don't come out in the right order. For instance, the image with width 400 comes first, instead of the one with width 640. My question is, what part of this query is changing the order from the way it is in @ProductContent? I'd rather just sort once before inserting into @ProductContent as the order by clauses in the XML query are a big performance hit.
