CODEWITHSUNDEEP
sqlsql-server
user13771510
user13771510

Reputation: 11

SQL Server result does not match the Oracle results

This is my Oracle SQL statement:

select 
    ACCOUNTID as ACCOUNTID,
    CREATEDDATE as CREATEDDATE,
    OLDVALUE as OLDVALUE 
from 
    accounthistory_n_dim 
where 
    rowid in (select A.ROWID 
              from 
                  (select 
                       accountid,
                       row_number() over (partition by accountid order by CREATEDDATE desc) as ROWNUMBER,
                       CREATEDDATE, ROWID 
                   from 
                       accounthistory_n_dim 
                   where 
                       field = 'CSM_Segment__c') A  
              where 
                  A.ROWNUMBER = 1)

I converted this Oracle code into this SQL Server T-SQL statement:

SELECT  
    us.CREATEDDATE AS CREATEDDATE,  
    us.OLDVALUE AS OLDVALUE,  
    us.ACCOUNTID AS ACCOUNTID 
FROM 
    (SELECT
         A.ACCOUNTID,
         A.OLDVALUE,
         ROW_NUMBER() OVER (PARTITION BY A.accountid ORDER BY A.CREATEDDATE DESC) AS ROWNUMBER,
         A.CREATEDDATE
     FROM
         sales_ent.accounthistory_n_dim_bt A 
     WHERE 
         A.field = 'CSM_Segment__c') us
WHERE 
    us.ROWNUMBER = 1

But the results do not match, since the Oracle rowid equivalent is not present in SQL Server.

Is my conversion wrong, from Oracle to SQL Server? Any thoughts?

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In either database, I think this would be a better approach:

select ah.* 
from (select ah.*,
             row_number() over (partition by accountid order by createddate desc) as seqnum
      from accounthistory_n_dim ah
      where ah.field = 'CSM_Segment__c'
     ) ah
where seqnum = 1;

This gets the most recent row for each account with the specified field.

Upvotes: 1

Related Questions