Finding the second highest number in SQL Server can be achieved using various methods. Here are two common approaches:

Using the TOP and ORDER BY Clause

This method involves using the TOP clause twice with ORDER BY to find the second highest number.

SELECT TOP 1 column_name
FROM (
    SELECT DISTINCT TOP 2 column_name
    FROM table_name
    ORDER BY column_name DESC
) AS temp
ORDER BY column_name ASC;

Using ROW_NUMBER() Function

This method utilizes the ROW_NUMBER() function to assign a unique number to each row, ordered by the column of interest.

WITH NumberedRows AS (
    SELECT column_name, ROW_NUMBER() OVER (ORDER BY column_name DESC) AS row_num
    FROM table_name
)
SELECT column_name
FROM NumberedRows
WHERE row_num = 2;

Example

Consider a table Employees with a column Salary. To find the second highest salary:

Using TOP and ORDER BY:

SELECT TOP 1 Salary
FROM (
    SELECT DISTINCT TOP 2 Salary
    FROM Employees
    ORDER BY Salary DESC
) AS temp
ORDER BY Salary ASC;

Using ROW_NUMBER():

WITH NumberedSalaries AS (
    SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS row_num
    FROM Employees
)
SELECT Salary
FROM NumberedSalaries
WHERE row_num = 2;

Both methods will return the second highest salary from the Employees table. Choose the method that best fits your requirements and database design. If you have a specific dataset or more complex scenarios, feel free to share, and I can help tailor the query to your needs!