SELECT
 Objects_tbl.*,
 Ir.ObjectPuid AS IrPuid,
 Ir.ObjectName AS IrName
FROM
  dbo.Objects_tbl WITH (nolock)
 LEFT OUTER JOIN dbo.Objects_Ir_Rel_tbl WITH (nolock) ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
 LEFT OUTER JOIN dbo.Objects_tbl Ir WITH (nolock) ON (dbo.Objects_Ir_Rel_tbl.IrId = Ir.Id)
WHERE Objects_tbl.Id in
 (SELECT Objects_Ir_Rel_tbl.ObjectId
  FROM Objects_Ir_Rel_tbl WITH (nolock)
  WHERE Objects_Ir_Rel_tbl.IrId in
  (SELECT TOP 10
    Objects_Ir_Rel_tbl.IrId
   FROM
    Objects_tbl WITH (nolock)
    INNER JOIN Objects_Ir_Rel_tbl WITH (nolock) ON (Objects_tbl.Id = Objects_Ir_Rel_tbl.ObjectId)
   WHERE
     Objects_tbl.ObjectClass='DS'
 -- AND Objects_tbl.ProcStateColRf = 'N'
 AND Objects_tbl.ProcStateColRf = 'N'
 AND Objects_tbl.ObjectFromSiteID= 'TCP010'
 AND Objects_tbl.ObjectOwningSiteID= 'TCP010' -- 'TCT'
 AND Objects_tbl.ObjectUgMaster_Type = 'SP'
 AND Objects_tbl.ObjectType IN ('UGMASTER','UGPART')
ORDER BY  Objects_tbl.ObjectLastModDate DESC
   )
  )
and Objects_tbl.ObjectType IN ('UGMASTER','UGPART')
ORDER BY
 Ir.ObjectLastModDate DESC,
 Ir.ObjectName,
(CASE Objects_tbl.ObjectType
  WHEN 'UGMASTER' THEN 11
  WHEN 'UGALTREP' THEN 12
  WHEN 'UGMANIFESTATION' THEN 13
  WHEN 'UGPART' THEN 14
  ELSE 19 END) ASC
