SQL - Incorrect syntax near 'LIMIT'

Hello, I am currently developing an Outsystems web app, and I'm using an advanced query. However I can't understand why my sql gives the error in the title.
The query works fine if I remove the LIMIT part, but I need it in order to do pagination on the table.
Here is the sql query:


SELECT {PeoplePersonal}.[FirstName], {ProjectAllocations}.[Percentage],
    (SELECT SUM (PA.[Percentage])
    FROM {ProjectAllocations} AS PA
    INNER JOIN {PeoplePersonal} ON {PeoplePersonal}.[Id] = PA.[PeopleId]
    INNER JOIN {Projects} ON {Projects}.[Id] = PA.[ProjectId]
    WHERE {PeoplePersonal}.[Id] = {ProjectAllocations}.[PeopleId] and {Projects}.[FinishDate] = '1900-01-01'),{PeoplePersonal}.[Id], {ProjectAllocations}.[Id], {Projects}.[FinishDate]

FROM {ProjectAllocations} 
INNER JOIN {PeoplePersonal} ON {PeoplePersonal}.[Id] = {ProjectAllocations}.[PeopleId]
INNER JOIN {Projects} ON {Projects}.[Id] = {ProjectAllocations}.[ProjectId]
WHERE {ProjectAllocations}.[ProjectId] = @ProjectId and {ProjectAllocations}.[Percentage]>0 
LIMIT @LimitRecord OFFSET @OffsetRecord;

Hi Nuno,

In Sql Query You can use Top or below Query to limit the records -

OFFSET @OffsetRecord ROWS 
FETCH NEXT @LimitRecord ROWS ONLY


Thanks

Vinod

Thank you for your answer it was mostly correct. However there was one more thing missing which was the order by that I eventualy figured out. Because OFFSET-FETCH is applied only on ordered sets.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.