0

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 enter image description here

Thank you, I really appreaciate your answer

  • So, you want the names of all employees whose hire date was less than the oldest London hire? Looks OK if that's what you want from your query! Strange requirement, but if that's it - looks like you nailed it! – Vérace Jan 21 '18 at 13:08
  • I needed to write out employees who work longer than any employee of London.. Now I need 2nd way how to write that.. but I'm like there is no way to write that.. :/ – Jakub Jan 21 '18 at 13:13
  • I think the query is fine as it is - it gives you your required data - why do feel the need to write it a different way? – Vérace Jan 21 '18 at 13:15
  • I had to make some queries and write them in 2 ways as my homework.. and I'm stucked – Jakub Jan 21 '18 at 13:18
  • Sometimes good enough is good enough - leave that query alone. There are probably many more queries that could be written better in different ways - this isn't one of them IMHO! :-) – Vérace Jan 21 '18 at 13:19
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

  • 3
    Minor nitpick: this will return no rows if there is at least one employee in London with NULL in HireDate. dbfiddle.uk/… – ypercubeᵀᴹ Jan 21 '18 at 14:01
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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.