Query from Postgres to SQL Server
applicant_degree_1 its a subquery that contains:

SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees     
WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1



  The query above try to get the greatest id from applicant_degrees for each college_attended_id.


Then, I beleive your query can run on MSSQL like this:

SELECT applicant_majors.*
FROM applicant_majors 
WHERE applicant_majors.applicant_degree_id IN (
    SELECT MAX(id) FROM applicant_degrees 
    WHERE applicant_degrees.college_attended_id = college_attendeds.id
)
ORDER BY id 
LIMIT 1 OFFSET 1



  Notice that I changed the ORDER BY and LIMIT for a MAX aggregate function.


Hope it helps.