why my code doesn't work???
select d.Name as Department, e.Name as Employee, e.Salary
from Employee e, Department d
where e.DepartmentId = d.Id
and Salary in (select Salary from Employee
group by DepartmentId
having count(Salary) <3
order by Salary desc
I think it is hard to use GROUP BY when you want to query out more than one values from each group. A substitute for GROUP BY in this case could be something like this:
SELECT COUNT(DISTINCT(e2.Salary)) FROM Employee e2 WHERE e2.Salary >= e1.Salary AND e2.DepartmentId = e1.DepartmentId ) <= 3
Two conditions, one is to count how many values there are greater than the current selected salary; the other one is to specify the group.