How to formulate this SQL query?

Question

I want to get employee numbers, and start datesall employees start datesequal to the earliest date.

I know this is wrong. But just write to show what I want.

SELECT     start_date, employee_no
FROM         [employees]
WHERE     (start_date = MIN(start_date))

Answer 1

You were close!

SELECT     start_date, employee_no
FROM         [employees]
WHERE     start_date = (SELECT MIN(start_date) FROM employees)

Answer 2

For this, I would use a table of common expressions (CTE). This is similar to creating a temporary table.

;with EmpInfo as 
(
    SELECT start_date, employee_no, MIN(start_date) OVER () as MinStartDate
    FROM [employees]
)
SELECT start_date, employee_no FROM EmpInfo WHERE start_date = MinStartDate

Here is the Microsoft web page about Using Common Table Expressions

Answer 3

:

SELECT     start_date, employee_no 
FROM         [employees] 
WHERE     start_date = (select MIN(start_date) from [employees])

Answer 4

SELECT    start_date, employee_no
FROM      [employees]
WHERE     start_date = (SELECT MIN(start_date) FROM [employees])