How to Write Over Partition By SQL Query

Here is how you can limit rows by row number and Over partition by statements in Oracle SQL.

For instance, in a year the launches of a satellite are more than 2. In that case, the output should print up to ‘2’ rows. Here is an SQL query to achieve it.

Over partition by


  1. Over and Partition By
  2. SQL Row number Over Partition by

Over and Partition By

The over keyword’s purpose is to get the row number that has first entry in the year. The partition by and order by statements make the data into small partition.

Here is an SQL query prints upto 2 rows if satellite launches are more than “2” in a year. Created two tables. Launch and Satellite. Inserted data and make ready to use these tables.

--create table satellite(official_name char(20), launch_id number);
--insert into satellite values('abc1', 1001);
--insert into satellite values('abc2', 1002);
--insert into satellite values('abc3', 1003);
--insert into satellite values('abc4', 1004);
-- into satellite values('abca1', 1005);
--insert into satellite values('abca2', 1006);
--create table launch(launch_date Date, launch_id number);
--insert into launch values ('01-JAN-1970', 1001);
--insert into launch values ('01-FEB-1971', 1002);
--insert into launch values ('01-MAR-1972', 1003);
--insert into launch values ('01-APR-1973', 1004);
--insert into launch values ('01-JAN-1970', 1005);
--insert into launch values ('01-JAN-1970', 1006);

SQL Row number Over Partition by

--Prints per year 2 rows if more than 2 satelites launched
select launch_date, official_name
      to_char(launch_date, 'YYYY-MM-DD') launch_date,
      row_number() over 
         partition by trunc(launch_date, 'year')
         order by launch_date
      ) first_n_per_year
   from satellite
   join launch
      on satellite.launch_id = launch.launch_id
   order by launch_date, official_name
where first_n_per_year <= 2
order by launch_date, official_name;

The sub-query result

Here it prints row number. The same query you can use to get ranks.

The sub-query result

The output achieved

Table output

The satellite launches were more than 2 in 1970. So in the output, it prints only two rows for the year 1970. It shows that you can control rows using Over Partition By SQL with a condition you need.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.