使用mssql在'LIMIT'附近的语法不正确

I'm trying to retrieve some data from the database, which need to be the top 10 of the agents with the highest score.

我正在尝试从数据库中检索一些数据,这些数据需要是得分最高的代理商的前十名。

My Query:

SELECT AgentScores.agentID, 
       AgentScores.totalScore, 
       Agents.firstname, 
       Agents.lastname 
FROM AgentScores 
INNER JOIN Agents ON AgentScores.AgentId=Agents.Agent_id 
ORDER BY AgentScores.totalScore DESC 
LIMIT 10

The inner joins are working. I've found the SELECT TOP 10 sql statement but.. I want the 10 agents with the highest score and not the first 10 id's. As you can see I'm ordering on the totalscore.

内连接正在工作。我已经找到了SELECT TOP 10 sql语句但是..我想要10个代理人得分最高而不是前10个id。正如你所看到的,我订购了总计。

Anyone has a clue how to fix this?

任何人都有一个线索如何解决这个问题?

Error: Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 102 [code] => 102 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'LIMIT'. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'LIMIT'. ) )

错误:数组([0] =>数组([0] => 42000 [SQLSTATE] => 42000 [1] => 102 [代码] => 102 [2] => [Microsoft] [SQL Server的ODBC驱动程序11 ] [SQL Server]'LIMIT'附近的语法不正确。[message] => [Microsoft] [SQL Server的ODBC驱动程序11] [SQL Server]'LIMIT'附近的语法不正确。))

Thank you!

2 个解决方案

#1

You have to use TOP clause instead of LIMIT

你必须使用TOP子句而不是LIMIT

SELECT TOP 10 AgentScores.agentID, AgentScores.totalScore, Agents.firstname, Agents.lastname FROM AgentScores INNER JOIN Agents ON AgentScores.AgentId=Agents.Agent_id ORDER BY AgentScores.totalScore DESC

#2

In order to limit rows in MSSQL, you have to use SELECT TOP 10 .... instead of LIMIT 10 (limit is a MySQL clause, not MSSQL)

为了限制MSSQL中的行,你必须使用SELECT TOP 10 ....而不是LIMIT 10(limit是一个MySQL子句,而不是MSSQL)

#1