SQL Server Is it possible to use TOP N WITH TIES with Union?

luaexgnf 于 2023-04-19 发布在其他

关注(0)|答案(1)|浏览(161)

I am trying to find the housekeeper and the guide with the most tasks in their respective tables, and I am trying to do this with union to show in one result, but I'm getting this error:

"The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause."

SELECT TOP 1 with ties e.EmpID as 'EmployeeID', Count(ScheduleID) as 'Task Count'
FROM EMPLOYEE e, CLEANING c
where e.empid = c.hkid
group by e.empid
UNION
SELECT TOP 1 with ties e.EmpID as 'EmployeeID', Count(ResID) as 'Task Count'
FROM EMPLOYEE e, RESERVATION r
where e.empid = r.guideid
group by e.empid
order by 'Task Count' desc

sql-server

来源：https://stackoverflow.com/questions/76003394/is-it-possible-to-use-top-n-with-ties-with-union

1条答案

按热度按时间

w41d8nur1#

Try this one:

SELECT *
FROM
(
    SELECT TOP 1 with ties e.EmpID as 'EmployeeID', Count(ScheduleID) as 'Task Count'
    FROM EMPLOYEE e, CLEANING c
    where e.empid = c.hkid
    group by e.empid
    order by [Task Count] desc
)  DS
UNION
SELECT *
FROM
(
    SELECT TOP 1 with ties e.EmpID as 'EmployeeID', Count(ResID) as 'Task Count'
    FROM EMPLOYEE e, RESERVATION r
    where e.empid = r.guideid
    group by e.empid
    order by [Task Count] desc
) DS

or this one:

SELECT SELECT TOP 1 with ties EmployeeID, [Task Count]
FROM
(
    SELECT e.EmpID as 'EmployeeID', Count(ScheduleID) as 'Task Count'
    FROM EMPLOYEE e, CLEANING c
    where e.empid = c.hkid
    group by e.empid
    UNION
    SELECT ties e.EmpID as 'EmployeeID', Count(ResID) as 'Task Count'
    FROM EMPLOYEE e, RESERVATION r
    where e.empid = r.guideid
    group by e.empid
    
) DS
order by [Task Count] desc

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server Is it possible to use TOP N WITH TIES with Union?

1条答案

相关问题

热门标签

最新问答