Resolved: Using HAVING without GROUP BY
I was wondering, what would be the SQL statement for the following: get all employees hired after 1 january 1999, whose names are counted less than 200 times but don't return grouped results.
Thus, each unique employee, independent of having a double name, should be returned as a single row - meaning names can not be grouped.
Based on how WHERE and HAVING clauses works (as explained in the lecture), I think we can use HAVING without GROUP BY, but it will work just like WHERE after the SELECT statement. That is because SQL works in the order of FROM, WHERE, and SELECT. Only then will other clauses proceed, such as GROUP BY, HAVING, and ORDER BY.
But, I was able to pull the output you wanted by inserting the code used in the lecture as a subquery, shown below:
SELECT * FROM employees WHERE first_name IN ( SELECT first_name FROM employees WHERE hire_date > '1999-01-01' GROUP BY first_name HAVING COUNT(first_name) < 200 ) ORDER BY first_name ASC, last_name ASC;
and here's a subset of the output:
Hope this helps,