MS Dynamics CRM Metadata query sample

The following is a handy query example to look for entity/attribute/option set lookup value:

select
e.Name as Entity
,os.IsGlobal
,a.Name as Attribute
,aop.DisplayOrder
,aop.Value as OptionValue
,ol.Label as OptionLabel
,osl.Label as OptionSetLabel
,al.Label as AttributeLabel
,el.Label as EntityLabel
,e.IsAudited as EntityIsAudited
,os.Name as OptionSet
,e.IsAuditEnabled as EntityIsAuditEnabled
,al.Label as AttributeLable
,a.IsAuditEnabled as AttributeIsAuditEnabled
,atp.Description as AttributType
--,a.*
--,ol.*
--,al.*
from MetadataSchema.Entity e
inner join MetadataSchema.LocalizedLabel el on el.ObjectId=e.EntityId and el.LanguageId=1033 and el.ObjectColumnName='LocalizedName' and year(el.OverwriteTime)=1900
inner join MetadataSchema.Attribute a on a.EntityId=e.EntityId and a.IsCustomField=1 and year(a.OverwriteTime)=1900
inner join MetadataSchema.LocalizedLabel al on al.ObjectId=a.AttributeId and al.LanguageId=1033 and al.ObjectColumnName='DisplayName' and year(al.OverwriteTime)=1900
inner join MetadataSchema.AttributeTypes atp on atp.AttributeTypeId=a.AttributeTypeId
inner join MetaDataSchema.OptionSet os on os.OptionSetId=a.OptionSetId and os.IsCustomOptionSet=1
inner join MetadataSchema.LocalizedLabel osl on osl.ObjectId=os.OptionSetId and osl.LanguageId=1033 and osl.ObjectColumnName='DisplayName' and year(osl.OverwriteTime)=1900
inner join MetadataSchema.AttributePicklistValue aop on aop.OptionSetId=os.OptionSetId and year(aop.OverwriteTime)=1900
inner join MetaDataSchema.LocalizedLabel ol on ol.ObjectId=aop.AttributePicklistValueId and ol.ObjectColumnName='DisplayName' and year(ol.OverwriteTime)=1900 and ol.LanguageId=1033
where year(e.OverwriteTime)=1900
--and e.Name='new_entity'
and e.Name='opportunity'
--and e.IsCustomEntity=1
order by 1,2 desc,3,4

year(...)=1900 can be replaced by =0

Find n-th element with t-sql

Here are few methods to get n-th element from a result set (or a table).
1) using sub query
--http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server

 use master
 go
 with T as
 (
 select
   number
   ,low
   ,high
   ,status
   from master..spt_values
   where type='P'
 )

 select
  *
  from T as A
  where 4=(select count(distinct B.number) from T as B where B.number<A.number)
 go
2) for sql server 2005 and above, rely on row_number function
 use master
 go
 with T as
 (
 select
   number
   ,ROW_NUMBER() over(order by number) as NumberOrder
   ,DENSE_RANK() over(order by number) as NumberRank
   ,low
   ,high
   ,status
   from master..spt_values
   where type='P'
 )
 --select * from T
 --select top 1 * from (select top 5 * from T order by number) X order by number desc
 select
  *
  from T
  where NumberOrder=5
  --where NumberRank=5
 go
3) double flip:
-- first part is identical
--select * from T
--select top 1 * from (select top 5 * from T order by number) X order by number desc