Implementing Most Recently Used Lookups

-- This script demonstrates adaptive sorting of lookup lists

declare @SortMethod varchar(20)
set @SortMethod = 'MostCommonlyUsed'
set @SortMethod = 'MostRecentlyUsed'

-- create some test data
declare @states table (statecode varchar(2), sortseq int)
insert into @states values ('AL',0)
insert into @states values ('AK',0)
insert into @states values ('AZ',0)
insert into @states values ('AR',0)
insert into @states values ('CA',0)

declare @customer table (custid int, statecode varchar(2), createddate smalldatetime) -- statecode is FK to @states
insert into @customer values (1, 'AK', '1/1/2009')
insert into @customer values (2, 'AK', '2/2/2009') -- AK most popular (2 customers)
insert into @customer values (3, 'AR', '3/3/2009') -- AR most recent createddate

declare @hold table (statecode varchar(2), sortseq int)


-- Start by sorting all statecodes alphabetically
update
	s
set
	sortseq = a.sortseq
from
	@states s
join
	(
	select
		statecode,
		rank() over (order by statecode) as sortseq
	from
		@states
	) as a on s.statecode = a.statecode



-- set top 2 most recently used first using createddate from foreign key table
if @SortMethod = 'MostRecentlyUsed'
begin
	-- get top two most recently used statecodes from foreign key table
	insert into
		@hold
	select top 2
		statecode,
		sortseq
	from
		(
		select distinct
			statecode,
			-rank() over (order by createddate) as sortseq
		from 
			-- get max createddate per statecode
			(
			select 
				statecode, 
				(select max(c2.createddate) from @customer c2 where c.statecode=c2.statecode) as createddate 
			from
				@customer c
			group by
				statecode
			) as mru
		) as p
	order by 
		sortseq
end



-- set top 2 most popular first using counts from foreign key table
if @SortMethod = 'MostCommonlyUsed'
begin
	-- get top two used statecodes
	insert into
		@hold
	select top 2
		statecode,
		sortseq
	from
		-- get usage count of each statecode from foreign key table
		(
		select distinct
			statecode,
			-rank() over (order by count(*)) as sortseq
		from 
			@customer
		group by 
			statecode
		) as p
	order by 
		sortseq
end


-- save the new ordering nightly or monthly
update
	s
set
	sortseq = h.sortseq
from
	@states s
join
	@hold h on s.statecode = h.statecode


-- show the results
select * from @states order by sortseq