Adding an ORDER BY statement causes the SQL editor throwing an error. However from what I can judge there's nothing wrong with the statement. When I remove the ORDER BY statement everything is OK. The error thrown is 'Missing right parenthesis'. What I'm trying to do is delete everything except the last 1000 records for a specific user. 

SyntaxEditor Code Snippet

DELETE FROM {DebugData}

WHERE {DebugData}.[deviceId] = @DeviceId
AND {DebugData}.[type] = 'API'
AND {DebugData}.[Id] NOT IN 
(
    SELECT {DebugData}.[Id] FROM {DebugData}
    WHERE {DebugData}.[deviceId] = @DeviceId
    AND {DebugData}.[type] = 'API'
    AND ROWNUM <= 1000
    ORDER BY {DebugData}.[Id] DESC
)


Hello Jacco van der Spek


Can you please explain me what is ROWNUM? where you assign the value for ROWNUM?

Solution

Hello Jacco

First, I think you are using the ROWNUM in the wrong way if I understood what you are trying to achieve (I'm assuming you are runing your query against an ORACLE database, as there is no ROWNUM in SQL Server)

Please, take a look at this article: https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results

I can't reproduce exactly your query, as I don't have access to an Oracle database, but I did something similar with SQL Server and get no errors:

DELETE FROM {DebugData}

WHERE {DebugData}.[DeviceId] = @DeviceId
AND {DebugData}.[type] = 'API'
AND {DebugData}.[Id] NOT IN 
(
    SELECT TOP 1000 {DebugData}.[Id] FROM {DebugData}
    WHERE {DebugData}.[DeviceId] = @DeviceId
    AND {DebugData}.[type] = 'API'
    ORDER BY {DebugData}.[Id] DESC
)

I tested in OS11.

Are you using OS11 or OS10 (or other version)?

If you look into the article, it will show you that you need to do another sub query in order to first sort the records and only than limit by ROWNUM. Maybe doing this way the error goes away...

Cheers.

Solution

Eduardo Jauch wrote:

Hello Jacco

First, I think you are using the ROWNUM in the wrong way if I understood what you are trying to achieve (I'm assuming you are runing your query against an ORACLE database, as there is no ROWNUM in SQL Server)

Please, take a look at this article: https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results

I can't reproduce exactly your query, as I don't have access to an Oracle database, but I did something similar with SQL Server and get no errors:

DELETE FROM {DebugData}

WHERE {DebugData}.[DeviceId] = @DeviceId
AND {DebugData}.[type] = 'API'
AND {DebugData}.[Id] NOT IN 
(
    SELECT TOP 1000 {DebugData}.[Id] FROM {DebugData}
    WHERE {DebugData}.[DeviceId] = @DeviceId
    AND {DebugData}.[type] = 'API'
    ORDER BY {DebugData}.[Id] DESC
)

I tested in OS11.

Are you using OS11 or OS10 (or other version)?

If you look into the article, it will show you that you need to do another sub query in order to first sort the records and only than limit by ROWNUM. Maybe doing this way the error goes away...

Cheers.

Thanks very much for the article. I've now changed my query to:


DELETE FROM {DebugData}

WHERE {DebugData}.[deviceId] = @DeviceId
AND {DebugData}.[type] = 'API'
AND {DebugData}.[Id] NOT IN 
(
    SELECT {DebugData}.[Id] FROM (
        SELECT {DebugData}.[Id] FROM {DebugData}
        WHERE {DebugData}.[deviceId] = @DeviceId
        AND {DebugData}.[type] = 'API'
        ORDER BY {DebugData}.[Id] DESC
    )
    WHERE ROWNUM <= 1000
)


This is not giving any errors and should return what I'm looking for.

Nice :)