Announcement

Collapse
No announcement yet.

Row number in subselect

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Row number in subselect

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

    Code:
    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 ?

    Comment


    • #3
      I tried: same error.

      Comment


      • #4
        Have you tried something like shown below?

        Code:
        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.

        Comment

        Working...
        X