What is ROW_NUMBER() function in oracle ?

Before knowing the ROW_NUMBER() function we need to understand the oracle analytical functions: Analytical means to get more control & allowing to operate within a table’s column or data like:

  • Analytical functions also know as windowing functions. it mostly used with window query.
  • Row_number is oracle analytical function that is used to get unique number for each row what is in row or window query.

Here i will discuss one example so we will get better understanding on analytical function:

 

  • suppose we have one table : userid (int),profile_image(blob),image_id(int). the table have profile image based on different userid & data feed is as following.
  • user_id profile_image image_id
    101 hexadeciam imaage–size–20*20 1
    101 hexadeciam imaage–size–40*40 2
    101 hexadeciam imaage–size–60*60 3
    102 hexadeciam imaage–size–20*20 4
    102 hexadeciam imaage–size–40*40 5
    102 hexadeciam imaage–size–60*60 6
    103 hexadeciam imaage–size–20*20 7
    103 hexadeciam imaage–size–40*40 8
    103 hexadeciam imaage–size–60*60 9

    As per above table we have duplicate userid with different size of profile image now my requirement is to get the first image of every userid. so now it’s easy to get the one image of every userid as below:

 

select a.user_id,a.profile_image,a.image_id from
(select b.user_id,b.profile_image,b.image_id, row_number() over(partition by user_id order by image_id ) ana from table b)icp
where icp.ana=1

The above query will return each first profile image for every userid so we have used row_number() analytical function on userid field.

Advertisements

About Hiren Kubavat

ORACLE,MYSQL.PHP,JQUERY,JAVASCRIPT,AJAX,HTML,CSS,ANGULAR2,AWS, WORDPRESS,JOOMLA,DRUPAL,CAKEPHP,LARAVEL,CODEIGNITER,MVC & CMS.
This entry was posted in oracle, sql and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s