Ошибка incorrect syntax near 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.

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'. ) )

Thank you!

Zohar Peled

77.8k9 gold badges68 silver badges117 bronze badges

asked Jul 29, 2015 at 15:11

You have to use TOP clause instead of 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

answered Jul 29, 2015 at 15:18

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)

Zohar Peled

77.8k9 gold badges68 silver badges117 bronze badges

answered Jul 29, 2015 at 15:13

drmarvelousdrmarvelous

1,64310 silver badges18 bronze badges

Источник

Remove From My Forums

Question
User-406225890 posted

SelectCommand=»SELECT [UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] FROM [UserProfiles] ORDER BY [CreateDate] LIMIT 3,5″>

Thats my SQL statement, and its giving me an error saying.

The statement was working before the limit was added, so

SelectCommand=»SELECT [UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] FROM [UserProfiles] ORDER BY [CreateDate]»

Incorrect syntax near ‘LIMIT’.

Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ‘LIMIT’.

I’m pretty new to SQL, and i tried a few things but couldn’t fix it, can someone help me out? Thanks

Answers

User1096912014 posted

Your exact statement would be

select [UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] from (select[UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] , row_number() OVER (order by [CreateDate]) as RowNumber form [UserProfiles]) Derived where RowNumber between
4 and 9
- Marked as answer by
  
  Thursday, October 7, 2021 12:00 AM

Источник

I am trying to query only 10 records and using LIMIT in query, but it is giving me the following error :

An error occurred while checking the query syntax. Errors: Incorrect
syntax near ‘LIMIT’.

Following is my query:

SELECT SubscriberKey, EmailAddress 
    FROM MASTER_IMPORT
        WHERE EmailAddress LIKE "%gmail.com" LIMIT 10

Anything that I’m doing wrong here?

asked Sep 20, 2017 at 10:00

Ashutosh AroraAshutosh Arora

5581 gold badge10 silver badges29 bronze badges

SFMC uses T-SQL syntax, so you need to rewrite your query using the TOP expression instead of LIMIT.

SELECT TOP(10) SubscriberKey, EmailAddress 
FROM MASTER_IMPORT
WHERE EmailAddress LIKE "%gmail.com"

Also refer to official documentation for more details on the TOP expression:

Limits the rows returned in a query result set to a specified number
of rows or percentage of rows.

In a SELECT statement, always use an ORDER BY clause with the TOP
clause. This is the only way to predictably indicate which rows are
affected by TOP.

answered Sep 20, 2017 at 10:05

Источник

when I am using this command to update table in PostgreSQL 13:

UPDATE rss_sub_source 
SET sub_url = SUBSTRING(sub_url, 1, CHAR_LENGTH(sub_url) - 1) 
WHERE sub_url LIKE '%/'
limit 10

but shows this error:

SQL Error [42601]: ERROR: syntax error at or near "limit"
  Position: 111

why would this error happen and what should I do to fix it?

asked Jul 22, 2021 at 14:09

LIMIT isn’t a valid keyword in an UPDATE statement according to the official PostgreSQL documentation:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

^{Reference: UPDATE (PostgreSQL Documentation )}

Solution

Remove LIMIT 10 from your statement.

answered Jul 22, 2021 at 14:32

John K. N.John K. N.

15.7k10 gold badges45 silver badges100 bronze badges

You could make something like this

But a Limit without an ORDER BY makes no sense, so you must choose one that gets you the correct 10 rows

UPDATE rss_sub_source t1
SET t1.sub_url = SUBSTRING(t1.sub_url, 1, CHAR_LENGTH(t1.sub_url) - 1) 
FROM (SELECT id FROM rss_sub_source WHERE sub_url LIKE '%/' ORDER BY id LIMIT 10) t2 
WHERE t2.id = t1.id

answered Jul 22, 2021 at 14:51

nbknbk

7,7295 gold badges12 silver badges27 bronze badges

Источник

when I am using this command to update table in PostgreSQL 13:

UPDATE rss_sub_source 
SET sub_url = SUBSTRING(sub_url, 1, CHAR_LENGTH(sub_url) - 1) 
WHERE sub_url LIKE '%/'
limit 10

but shows this error:

SQL Error [42601]: ERROR: syntax error at or near "limit"
  Position: 111

why would this error happen and what should I do to fix it?

asked Jul 22, 2021 at 14:09

LIMIT isn’t a valid keyword in an UPDATE statement according to the official PostgreSQL documentation:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

^{Reference: UPDATE (PostgreSQL Documentation )}

Solution

Remove LIMIT 10 from your statement.

answered Jul 22, 2021 at 14:32

John K. N.John K. N.

15.7k10 gold badges45 silver badges100 bronze badges

You could make something like this

But a Limit without an ORDER BY makes no sense, so you must choose one that gets you the correct 10 rows

UPDATE rss_sub_source t1
SET t1.sub_url = SUBSTRING(t1.sub_url, 1, CHAR_LENGTH(t1.sub_url) - 1) 
FROM (SELECT id FROM rss_sub_source WHERE sub_url LIKE '%/' ORDER BY id LIMIT 10) t2 
WHERE t2.id = t1.id

answered Jul 22, 2021 at 14:51

nbknbk

7,7295 gold badges12 silver badges27 bronze badges

Источник

Я пытаюсь получить некоторые данные из базы данных, которые должны быть в топ-10 агентов с наибольшим количеством очков.

Мой Запрос:

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

внутренние соединения работают. Я нашел SELECT TOP 10 оператор sql, но.. Мне нужны 10 агентов с самым высоким баллом, а не первые 10 удостоверений личности. Как вы можете видеть, я заказываю на totalscore.

кто-нибудь знает, как это исправить?

ошибка: 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'. ) )

спасибо!

2 ответов

вы должны использовать предложение 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

автор: Gianluca Colombo

чтобы ограничить строки в MSSQL, вы должны использовать SELECT TOP 10 …. вместо LIMIT 10 (limit-это предложение MySQL, а не MSSQL)

Источник

Ошибка incorrect syntax near limit

Question

Answers

Solution

Solution

2 ответов

А вот еще интересные материалы: