http://sqle.ru/index.php?action=history&feed=atom&title=Oracle_PL%2FSQL%2FAnalytical_Functions%2FFirst_Last
Oracle PL/SQL/Analytical Functions/First Last - История изменений
2026-04-04T11:02:14Z
История изменений этой страницы в вики
MediaWiki 1.30.0
http://sqle.ru/index.php?title=Oracle_PL/SQL/Analytical_Functions/First_Last&diff=1440&oldid=prev
в 13:45, 26 мая 2010
2010-05-26T13:45:19Z
<p></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<tr style="vertical-align: top;" lang="ru">
<td colspan="1" style="background-color: white; color:black; text-align: center;">← Предыдущая</td>
<td colspan="1" style="background-color: white; color:black; text-align: center;">Версия 13:45, 26 мая 2010</td>
</tr><tr><td colspan="2" style="text-align: center;" lang="ru"><div class="mw-diff-empty">(нет различий)</div>
</td></tr></table>
http://sqle.ru/index.php?title=Oracle_PL/SQL/Analytical_Functions/First_Last&diff=1441&oldid=prev
Admin: 1 версия
2010-05-26T09:56:05Z
<p>1 версия</p>
<p><b>Новая страница</b></p><div>==FIRST and LAST functions to get the first and last values in an ordered group==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL> CREATE TABLE all_sales (<br />
2 year INTEGER,<br />
3 month INTEGER,<br />
4 prd_type_id INTEGER,<br />
5 emp_id INTEGER ,<br />
6 amount NUMBER(8, 2)<br />
7 );<br />
Table created.<br />
SQL><br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,1 ,1 ,21 ,16034.84);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,2 ,1 ,21 ,15644.65);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,3 ,2 ,21 ,20167.83);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,4 ,2 ,21 ,25056.45);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,5 ,2 ,21 ,NULL);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,6 ,1 ,21 ,15564.66);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,7 ,1 ,21 ,15644.65);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,8 ,1 ,21 ,16434.82);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,9 ,1 ,21 ,19654.57);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,10 ,1 ,21 ,21764.19);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,11 ,1 ,21 ,13026.73);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,12 ,2 ,21 ,10034.64);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,1 ,2 ,22 ,16634.84);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,1 ,2 ,21 ,26034.84);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,2 ,1 ,21 ,12644.65);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,3 ,1 ,21 ,NULL);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,4 ,1 ,21 ,25026.45);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,5 ,1 ,21 ,17212.66);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,6 ,1 ,21 ,15564.26);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,7 ,2 ,21 ,62654.82);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,8 ,2 ,21 ,26434.82);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,9 ,2 ,21 ,15644.65);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,10 ,2 ,21 ,21264.19);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,11 ,1 ,21 ,13026.73);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,12 ,1 ,21 ,10032.64);<br />
1 row created.<br />
SQL><br />
SQL> select * from all_sales;<br />
YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT<br />
---------- ---------- ----------- ---------- ----------<br />
2006 1 1 21 16034.84<br />
2006 2 1 21 15644.65<br />
2006 3 2 21 20167.83<br />
2006 4 2 21 25056.45<br />
2006 5 2 21<br />
2006 6 1 21 15564.66<br />
2006 7 1 21 15644.65<br />
2006 8 1 21 16434.82<br />
2006 9 1 21 19654.57<br />
2006 10 1 21 21764.19<br />
2006 11 1 21 13026.73<br />
2006 12 2 21 10034.64<br />
2005 1 2 22 16634.84<br />
2005 1 2 21 26034.84<br />
2005 2 1 21 12644.65<br />
2005 3 1 21<br />
2005 4 1 21 25026.45<br />
2005 5 1 21 17212.66<br />
2005 6 1 21 15564.26<br />
2005 7 2 21 62654.82<br />
2005 8 2 21 26434.82<br />
2005 9 2 21 15644.65<br />
2005 10 2 21 21264.19<br />
2005 11 1 21 13026.73<br />
2005 12 1 21 10032.64<br />
25 rows selected.<br />
SQL><br />
SQL> --FIRST and LAST functions to get the first and last values in an ordered group.<br />
SQL><br />
SQL> --You can use FIRST and LAST with the following functions: MIN(), MAX(), COUNT(), SUM(), AVG(), STDDEV(), and VARIANCE().<br />
SQL><br />
SQL> SELECT<br />
2 MIN(month) KEEP (DENSE_RANK FIRST ORDER BY SUM(amount))<br />
3 AS highest_sales_month,<br />
4 MIN(month) KEEP (DENSE_RANK LAST ORDER BY SUM(amount))<br />
5 AS lowest_sales_month<br />
6 FROM all_sales<br />
7 GROUP BY month<br />
8 ORDER BY month;<br />
HIGHEST_SALES_MONTH LOWEST_SALES_MONTH<br />
------------------- ------------------<br />
5 7<br />
SQL><br />
SQL><br />
SQL><br />
SQL> drop table all_sales;<br />
Table dropped.<br />
SQL><br />
SQL><br />
<br />
</source></div>
Admin