fork download
copy 
  1. Create table PersonA(Tbn number primary key, name varchar2(20), otd number, sal number); 
  2. --Табельный номер , имя, отдел , зарплата
  3.  
  4. Insert into PersonA(Tbn,name,otd,sal) values(1, 'Аня',10,9000);
  5. Insert into PersonA(Tbn,name,otd,sal) values(2, 'Саша',10,5500);
  6. Insert into PersonA(Tbn,name,otd,sal) values(3, 'Таня',10,7000);
  7. Insert into PersonA(Tbn,name,otd,sal) values(4, 'Ваня',20,2300);
  8. Insert into PersonA(Tbn,name,otd,sal) values(5, 'Олег',20,4300);
  9. Insert into PersonA(Tbn,name,otd,sal) values(6, 'Коля',20,3900);
  10. Insert into PersonA(Tbn,name,otd,sal) values(7, 'Таня',30,7000);
  11. Insert into PersonA(Tbn,name,otd,sal) values(8, 'Макс',30,9000);
  12. Insert into PersonA(Tbn,name,otd,sal) values(9, 'Таня',30,8500);
  13. Insert into PersonA(Tbn,name,otd,sal) values(10,'Макс',30,9900);
  14. Insert into PersonA(Tbn,name,otd,sal) values(11,'Олег',30,9900);
  15. Insert into PersonA(Tbn,name,otd,sal) values(12,'Макс',30,9900);
  16. Insert into PersonA(Tbn,name,otd,sal) values(13,'Макс',30,9900);
  17. Insert into PersonA(Tbn,name,otd,sal) values(14,'Макс',30,9900);
  18. Insert into PersonA(Tbn,name,otd,sal) values(15,'Макс',30,9900);
  19. Insert into PersonA(Tbn,name,otd,sal) values(16,'Макс',30,7000);
  20. Insert into PersonA(Tbn,name,otd,sal) values(17,'Таня',30,3500);
  21.  
  22. select name, otd, sal, 
  23. MIN(sal) OVER (partition by otd) min_sal,
  24. MAX(sal) OVER (partition by otd) max_sal,
  25. MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (partition by otd) min_keep_sal,
  26. MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (partition by otd) max_keep_sal,
  27. FIRST_VALUE(sal) OVER (partition by otd order by sal range between unbounded preceding and unbounded following) first_val_sal,
  28. LAST_VALUE(sal) OVER (partition by otd order by sal range between unbounded preceding and unbounded following) last_val_sal
  29. from personA;
Success #stdin #stdout #stderr 0s 18088KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
stderr
copy 
Error: near line 22: near "(": syntax error
https://ideone.com/u1AwEX
language:
SQLite (sqlite 3.27.2)
created:
6 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!