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.
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
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.
You must be logged in to post a comment.