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