Row number in subselect

Scriptcase 8 Bugs
, , ,
#1

In a pdf report application using MS SQL Server 2008, I want to create a subslect query including a row number field, like this:


SELECT 
    row_number() OVER (ORDER BY id) record_number,
    id,
    name,
    phone,
    address
FROM 
    customer
where
   id={id_customer}

I’m getting the error that is shown below when saving the app, and of course the field record_number is not created.

mssql_query(): Query failed | Script: /var/www/html/scriptcase8/prod/third/adodb/drivers/adodb-mssql.inc.php linha: 845

Is there any other way to do this?

#2

Did you try is with: as somefieldname ?

#3

I tried: same error. :frowning:

#4

Have you tried something like shown below?


SELECT	a.id,
	 a.stuff1,
	b.stuff2
  FROM (
                SELECT  id,
			stuff1,
                        ROW_NUMBER() OVER(PARTITION BY id ORDER BY ORDERDATE DESC) rn //Your normal order by should work fine
                    FROM SC.table_name
              ) a, SC.table2 b  
WHERE a.rn = 1 AND a.link = b.link

Now i renamed some stuff in there, it’s basically an SQL to show the latest ‘order’ of every id.
But basically what I’m trying to get at, have you tried it in a subquery like this. As otherly I’m not seeing that big of a difference except for that i have ROW_NUMBER uppercased, which shouldn’t make a difference as it should be case insensitive, maybe worth a try anyways otherwise.