IT:AD:SQL Server:HowTo:SQL:HowTo:MRU
Summary
Keeping track of a user's Most Recently Used values in a drop down is an easy way to make using software less expensive.
Process
SELECT L.Id, L.Value, MAX(Score) AS HScore
FROM SomeLookup AS L
LEFT JOIN MRU AS M
ON L.Id = M.FK
GROUP BY L.Id, L.Value
ORDER BY HScore DESC, Value
Gives:
Id Value HScore
----------- ---------- -----------
2 Whisky 9
3 Wine 7
1 Gin 6
5 Crack NULL
4 Soda NULL
But can be improved as follows:
SELECT L.Id, L.Value, Max (Score) As HScore
FROM SomeLookup As L
LEFT JOIN (SELECT TOP 2 * FROM MRU) AS M ON L.Id = M.FK
GROUP BY L.Id, L.Value
ORDER BY HScore DESC, Value
And maybe even something like:
SELECT L.Id, L.Value AS V, Max (Score) As HScore
FROM SomeLookup As L
LEFT JOIN (SELECT TOP 2 * FROM MRU) AS M
ON L.Id = M.FK
GROUP BY L.Id, L.Value
UNION
SELECT 0, '---------------', NULL FROM SomeLookup AS L2
UNION
SELECT Id, L2.Value as V, NULL FROM SomeLookup AS L2
ORDER BY HScore DESC, V
which gives:
Id Value HScore
----------- ---------- -----------
2 Whisky 9
3 Wine 7
1 Gin 6
5 Crack NULL
4 Soda NULL
Id V HScore
----------- --------------- -----------
2 Whisky 2
1 Gin 1
0 --------------- NULL
5 Crack NULL
1 Gin NULL
4 Soda NULL
2 Whisky NULL
3 Wine NULL