How to get MAX int but exclude specific int?

sql, sql-server-2008, tsqlt

Solution

select max(score) , empid
from table
where score < (select max(score) from table )
group by empid

Problem

I'd like to get the max integer from a specific column excluding a value that will always be the max if present. Data may look like the following: ``` score, empid 1 3 3 3 10 3 1 5 2 5 1 8 2 8 3 8 10 8 ``` In the above, I'd like MAX score less than 10. MAX(score) doesn't work in this case since it will bring back 10. Results should look like this: ``` score, empid 3 3 2 5 3 8 ``` Any suggestions?

Original source