Other way to write query

Question

I'm new to sql language and I'm learning about subqueries.. I have one question is there other way how to write that query?

SELECT Employees.FirstName, 
       Employees.LastName 
FROM   Employees 
WHERE  Employees.HireDate < (SELECT MIN(Employees.HireDate) 
                             FROM   Employees 
                             WHERE  Employees.City = 'London');

on northwind database schema

Thank you, I really appreaciate your answer

Answer 1

SELECT Employees.FirstName, 
       Employees.LastName 
FROM   Employees 
WHERE  Employees.HireDate < ALL (SELECT Employees.HireDate
                                 FROM   Employees 
                                 WHERE  Employees.City = 'London');

SQL ANY and ALL Operators

Answer 2

I won't post the final answer to your homework exercise but you should become familiar with the SELECT TOP(1) ... ORDER BY construct. For example, the query below could be used to get the birth date of the oldest employee.

SELECT TOP(1) BirthDate
FROM Employees
WHERE BirthDate IS NOT NULL
ORDER BY BirthDate

Answer 3

Knowing, and understanding, different options to obtain the same results is key to getting familiar with SQL, IMHO. No matter how obscure and esoteric the problem might be.

Anyway, I know I'm late to the party, but here's a different take on a subquery method..

SELECT FirstName, 
       LastName
FROM (
  SELECT Employees.FirstName, 
         Employees.LastName,
         count(CASE WHEN Employees.City = 'London' THEN 1 END) OVER (ORDER BY Employees.Hiredate) London_hires_to_date,
         City, -- These 2 columns not needed, have included them for visibility of what the inner query is doing
         Hiredate
  FROM   Employees 
) subq
WHERE London_hires_to_date = 0