Advanced SQL Order By

Jacco van der Spek

Solved

Question

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
)

12 Feb 2019

Eduardo Jauch

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.

See solution in context

12 Feb 2019

1 reply

12 Feb 2019

Show thread

Hide thread

Jacco van der Spek

Eduardo Jauch wrote:

Hello Jacco

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.

12 Feb 2019

Suhas Jamdade

Hello Jacco van der Spek

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

12 Feb 2019

Eduardo Jauch

Solution

Hello Jacco

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.

12 Feb 2019

1 reply

12 Feb 2019

Show thread

Hide thread

Jacco van der Spek

Eduardo Jauch wrote:

Hello Jacco

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.

12 Feb 2019

Eduardo Jauch

Nice :)

12 Feb 2019

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

See the full guidelines