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.