CODEWITHSUNDEEP
sqlsql-serverlimit
ESwift
ESwift

Reputation: 19

SQL where to use LIMIT in query

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

Upvotes: 0

Views: 159

Answers (3)

zip
zip

Reputation: 4061

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

Upvotes: 3

Htet Wai Yan Lin
Htet Wai Yan Lin

Reputation: 21

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

Upvotes: 2

Venkataraman R
Venkataraman R

Reputation: 13009

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;

Upvotes: 1

Related Questions