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

IN THIS PAGE

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
from
(
   select
      to_char(launch_date, 'YYYY-MM-DD') launch_date,
      official_name,
      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 output achieved

LAUNCH_DATE	OFFICIAL_NAME
1970-01-01	abc1
1970-01-01	abca2
1971-02-01	abc2
1972-03-01	abc3

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.