SQL where to use LIMIT in query

Question

I am trying to limit my results to 10. The below query returns all results without the 'LIMIT 10' but when I add LIMIT 10 it gives me an error saying

Incorrect syntax near LIMIT

What am I missing?

SELECT dbo.Organizations.cmoName AS "Organizations not quoted"
    ,dbo.Calls.kbpCallID AS "kbpCallID"
    ,MAX(dbo.CallLines.kblCallLineID) AS "kblCallLineID"
FROM (
    (
        (
            (
                dbo.Organizations LEFT OUTER JOIN dbo.Quotes ON dbo.Organizations.cmoOrganizationID = dbo.Quotes.qmpCustomerOrganizationID
                ) LEFT OUTER JOIN dbo.OrganizationIndustryTypeLinks ON dbo.Organizations.cmoOrganizationID = dbo.OrganizationIndustryTypeLinks.cmdOrganizationID
            ) LEFT OUTER JOIN dbo.Calls ON dbo.Organizations.cmoOrganizationID = dbo.Calls.kbpOrganizationID
        ) LEFT OUTER JOIN dbo.CallLines ON dbo.Calls.kbpCallID = dbo.CallLines.kblCallID
    )
WHERE dbo.Quotes.qmpQuoteID IS NULL
    AND dbo.Organizations.cmoCustomerStatus = 1
    AND dbo.OrganizationIndustryTypeLinks.cmdIndustryTypeID = 'DRAFTING  '
    AND (
        dbo.CallLines.kblAddedDate < DATEADD(dd, - 60, GETDATE())
        OR dbo.CallLines.kblCallID IS NULL
        )
GROUP BY dbo.Organizations.cmoName
    ,dbo.Calls.kbpCallID
ORDER BY dbo.Organizations.cmoName ASC LIMIT 10

Dale K · Accepted Answer · 2020-02-12 01:11:24Z

In SQL Server: You can use top 10 instead of limit as shown in the below query.

SELECT TOP 10 dbo.Organizations.cmoName AS "Organizations not quoted"
    , dbo.Calls.kbpCallID AS "kbpCallID"
    , MAX(dbo.CallLines.kblCallLineID) AS "kblCallLineID"
FROM (
    (
        (
            (
                dbo.Organizations LEFT OUTER JOIN dbo.Quotes ON dbo.Organizations.cmoOrganizationID = dbo.Quotes.qmpCustomerOrganizationID
                ) LEFT OUTER JOIN dbo.OrganizationIndustryTypeLinks ON dbo.Organizations.cmoOrganizationID = dbo.OrganizationIndustryTypeLinks.cmdOrganizationID
            ) LEFT OUTER JOIN dbo.Calls ON dbo.Organizations.cmoOrganizationID = dbo.Calls.kbpOrganizationID
        ) LEFT OUTER JOIN dbo.CallLines ON dbo.Calls.kbpCallID = dbo.CallLines.kblCallID
    )
WHERE dbo.Quotes.qmpQuoteID IS NULL
    AND dbo.Organizations.cmoCustomerStatus = 1
    AND dbo.OrganizationIndustryTypeLinks.cmdIndustryTypeID = 'DRAFTING  '
    AND (
        dbo.CallLines.kblAddedDate < DATEADD(dd, - 60, GETDATE())
        OR dbo.CallLines.kblCallID IS NULL
        )
GROUP BY dbo.Organizations.cmoName
    ,dbo.Calls.kbpCallID
ORDER BY dbo.Organizations.cmoName ASC

Dale K · Accepted Answer · 2020-02-12 01:11:09Z

2

If you use SQL Server you use top(10) instead of Limit 10.

edited Feb 12, 2020 at 1:11

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Feb 5, 2020 at 3:47

Htet Wai Yan Lin

212 bronze badges

Comments

Dale K · Accepted Answer · 2020-02-12 01:10:50Z

In SQL Server, you use TOP as suggested by @Zip.

From SQL Server 2012 onwards, you can use OFFSET... FETCH Read more on Offset... Fetch

SELECT dbo.Organizations.cmoName AS "Organizations not quoted"
    ,dbo.Calls.kbpCallID AS "kbpCallID"
    ,MAX(dbo.CallLines.kblCallLineID) AS "kblCallLineID"
FROM (
    (
        (
            (
                dbo.Organizations LEFT OUTER JOIN dbo.Quotes ON dbo.Organizations.cmoOrganizationID = dbo.Quotes.qmpCustomerOrganizationID
                ) LEFT OUTER JOIN dbo.OrganizationIndustryTypeLinks ON dbo.Organizations.cmoOrganizationID = dbo.OrganizationIndustryTypeLinks.cmdOrganizationID
            ) LEFT OUTER JOIN dbo.Calls ON dbo.Organizations.cmoOrganizationID = dbo.Calls.kbpOrganizationID
        ) LEFT OUTER JOIN dbo.CallLines ON dbo.Calls.kbpCallID = dbo.CallLines.kblCallID
    )
WHERE dbo.Quotes.qmpQuoteID IS NULL
    AND dbo.Organizations.cmoCustomerStatus = 1
    AND dbo.OrganizationIndustryTypeLinks.cmdIndustryTypeID = 'DRAFTING  '
    AND (
        dbo.CallLines.kblAddedDate < DATEADD(dd, - 60, GETDATE())
        OR dbo.CallLines.kblCallID IS NULL
        )
GROUP BY dbo.Organizations.cmoName
    ,dbo.Calls.kbpCallID
ORDER BY dbo.Organizations.cmoName ASC OFFSET 0 ROWS

FETCH 10 ROWS ONLY;

Collectives™ on Stack Overflow

SQL where to use LIMIT in query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related