Variable With TOP Keyword Can't Be Used Together

Question

So in the query below, I am getting the error that there is incorrect syntax next to the keyword TOP and ORDER BY. I don't see a syntax issue with either. After further testing, it seems that my database platform doesn't allow TOP and ORDER BY in a derived table. Does anyone see a workaround for this query? I can't seem to find another way to have an integer variable after TOP. The unfortunate platform that I am using is SqlDbx.

DECLARE @SQL VARCHAR(16384)
DECLARE @CC_N INT

SELECT 
    @CC_N = B.PP - A.CC_PP 
FROM
(
    SELECT 
        Carline, 
        SUM(PP_Floor) AS CC_PP
    FROM PP_Balancing
    WHERE Carline = '01'
    GROUP BY Carline
) AS A
JOIN PP_National AS B ON A.Carline = B.Carline

SET @SQL = 
'
UPDATE PP_Balancing
SET PP_Floor = PP_Floor + 1
WHERE
Sales_Locality IN
    (
        SELECT TOP '+CAST(@CC_N AS VARCHAR(255))+' 
            Sales_Locality
        FROM PP_Balancing
        WHERE Carline = ''01''
        ORDER BY PP_Decimal DESC
    )
AND Carline = ''01''
'

EXEC (@SQL)

Answer 1

OP mentioned in comments that this is a Sybase ASE 12.5.4 instance, so we'll look at some ASE specific details ...

OP hasn't (yet) provided the actual error message but I'm guessing it looks something like:

select * from
(select top 4 id
     from    sysobjects
     order by name
) dt
go

Msg 154, Level 15, State 53:
Server 'ASE200', Line 2:
An ORDER BY clause is not allowed in a derived table.

select * from
(select id
     from    sysobjects
     order by name
) dt
go

Msg 154, Level 15, State 53:
Server 'ASE200', Line 3:
An ORDER BY clause is not allowed in a derived table.

And this is the expected behavior (per the Sybase ASE manuals), ie, order by is not allowed in a sub-query or derived table.

And while the top clause is allowed in a sub-query (or derived table), the results will likely not come out as expected (nor be guaranteed to generate the same results on repeated runs) without an order by clause.

So that leaves use with the more important question of how to update just the 'top X' number of rows.

Now, Sybase ASE does allow the top clause in an update statement but the lack of support for an order by clause (in the update statement) makes the top pretty much useless if, as in this case, a desired ordering must be applied.

Since the OP is using a variable (@CC_N) to determine the number of rows to update, I'm going to assume we can use another variable to determine the range of PP_Decimal values we want to update.

Before I get to the actual update statement we need to look at a couple intermediate steps ...

-- use variable (@name) to capture the Nth name from sysobjects (order by name)

select top 5 name from sysobjects order by name
go

 name
 ------------------------------
 sysalternates
 sysattributes
 syscolumns
 syscomments
 sysconstraints         <<<=== we want to capture this value in @name

(5 rows affected)

declare @name varchar(255)

-- @name will be assigned each value as it's returned by the query, with
-- the last value (sysconstraints) being the last value assigned to @name

select top 5 @name = name from sysobjects order by name

print @name
go

(5 rows affected)
sysconstraints          <<<=== the contents of @name

In this example I plugged in a static 5, but in the OPs query we need to plug in a variable (@CC_N), which is going to require that we dynamically build and execute a query. But in our case it gets a bit more interesting in that for our dynamic query we also need to capture the results of the query into @name so we can use it later on. Lucky for us, ASE allows us to do just this by including our @name in the dynamically created query, eg:

declare @name   varchar(30),
        @SQL    varchar(100),
        @CC_N   int

select  @CC_N = 5

select @SQL = 'select top ' + convert(varchar(30),@CC_N) + ' @name = name from sysobjects order by name'

select @SQL as 'my query'

exec(@SQL)

select @name as '@name'
go

 @SQL
 -------------------------------------------------------
 select top 5 @name = name from sysobjects order by name

 @name
 ------------------------------
 sysconstraints          <<<=== the contents of @name

At this point we should have everything we need to implement the desired update.

NOTE: For the sake of this answer I'm going to assume the PP_Decimal column is an integer.

DECLARE @SQL        varchar(1000),
        @CC_N       int,
        @PP_Decimal int

-- OPs original code to find the Nth value;
-- removed the superfluous 'group by' from the derived table

SELECT  @CC_N = B.PP - A.CC_PP 
FROM
(SELECT SUM(PP_Floor) AS CC_PP
 FROM   PP_Balancing
 WHERE Carline = '01'
) AS A
JOIN PP_National AS B ON A.Carline = B.Carline

-- ??? should OP check for @CC_N >= 1 ???

-- find the Nth PP_Decimal value where 'N' == @CC_N

select @SQL =
"select top " + convert(varchar(30), @CC_N) + " @PP_Decimal = PP_Decimal
 from   PP_Balancing
 where  Carline = '01'
 order by PP_Decimal desc"

-- comment-out/remove the following 'select';
-- only placed here for debugging purposes

select @SQL as '@SQL'

exec(@SQL)

-- at this point @PP_Decimal should contain the last/Nth PP_Decimal value when ordered by PP_Decimal desc;
-- again, following 'select' is for debugging purposes

select @PP_Decimal as '@PP_Decimal'

-- now update our table where PP_Decimal >= @PP_Decimal

update PP_Balancing
set    PP_Floor    = PP_Floor + 1
where  PP_Decimal >= @PP_Decimal
and    Carline     = '01'
go

 @SQL                                                                                                                                                                                                                                                                                                                                                                                                 
 ---------------------------------------
 select top 5 @PP_Decimal = PP_Decimal     <<<=== for sake of example I plugged in @CC_N=5
 from   PP_Balancing
 where  Carline = '01'
 order by PP_Decimal desc

 @PP_Decimal
 -----------
         538          <<<=== made up number for the sake of this example (since I don't have any actual data)

 (N rows affected)    <<<=== assuming update statement finds @CC_N rows to update

NOTE: This solution assumes that PP_Decimal values are unique, otherwise the final update could update more than @CC_N values, eg,

• assume max(PP_Decimal) = 47
• assume there are 100 rows with PP_Decimal = 47
• assume @CC_N = 5
• @PP_Decimal would be set to 47 and
• instead of affecting just @CC_N=5 rows, the update would update all 100 rows where PP_Decimal >= 47

Some variations the user could attempt would be to limit the number of rows to be updated, eg:

update top 5 ...

or

set rowcount 5
update ...
set rowcount 0

But these won't guarantee the same rows would be updated on repeated runs.

One other (obvious) solution would be to pull the top @CC_N primary key (PK) column values into a #temp table and then have the update join with this #temp table to perform the desired @CC_N updates. I'll wait to see if a #temp table solution is acceptable to the OP and/or let someone else post an answer w/ the details of a #temp table based solution.