1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Can I rewrite the following query without using Row_number() function ??!!

Discussion in 'General' started by mounish, Jan 13, 2011.

  1. mounish

    mounish Guest

    Hello every one, can I rewrite the following query without using the 'ROW_NUMBER() OVER ' part.
    The query is supposed to pull out the records whose CODE is not NULL and has most
    recent date for UPDATE_DATE . The reason I wanted to do this is, When I embed this query
    in between many other queries along with JOINs, My oracle server is unable to execute. So, I thought
    its better to supplant 'ROW_NUMBER() OVER ' logic with something else and try it. .
    Code (SQL):

    SELECT a.* FROM
      (SELECT b.*, ROW_NUMBER() OVER (PARTITION BY b.PIDM
       ORDER BY b.UPDATE_DATE DESC) AS Rno
        FROM
         (
          SELECT  *
          FROM SHYNCRO WHERE CODE IS NOT NULL
          )b
      )a
    WHERE a.Rno = 1

     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post the error you're seeing when you use this query as you've described.