View with most common value AS Column_Name

[deanbri75]

I have a table with a column containing a term code (200810, 200820, 200730, etc).

I want to create a view (we'll call it View2) with a column value with the most common term code for every record in the View2.

I can get the value I want by using the following select query in a separate View (called View1)

SELECT     TOP 1 SLBRMAP_FROM_TERM
FROM         dbo.APPLICATION
GROUP BY SLBRMAP_FROM_TERM
ORDER BY COUNT(*) DESC

My problem is how to incorporate this value into the column of my view.

I'd like to use that select query AS Column_Name in my view.

I can do something like this in View2 just referencing the results of View1. It works but seems like there is a better way.

SELECT     dbo.View1.SLBRMAP_FROM_TERM, dbo.APPLICATION.AREA_PREF1, dbo.APPLICATION.AREA_PREF2, dbo.APPLICATION.AREA_PREF3
FROM         dbo.APPLICATION CROSS JOIN
                      dbo.View1

thanks,
brian