Analytic function in postgresql - continue.....

list of analytic function available in postgresql is

• row_number()
• rank()
• dense_rank() 
• lag(value any [, offset integer [, DEFAULT any ]])
• lead(value any [, offset integer [, DEFAULT any ]])
• first_value(value any)
• last_value(value any)
• nth_value(value any, nth integer)
• percent_rank()
• cume_dist()
• ntile(num_buckets integer)

you could find analytic function marked over green posted here,

here let look on remaining functions.

FIRST_VALUE and LAST_VALUE function

Syntax

FIRST_VALUE() OVER () or LAST_VALUE() OVER()

  The FIRST_VALUE analytic function picks the first record
from the partition after doing the ORDER BY. The  is computed
on the columns of this first record and results are returned.

  The LAST_VALUE function is used in similar context except
that it acts on the last record of the partition. But it not
picking up the right record instead its pick up same data
what salary column picks.

example

don=#  select person, dept, salary,first_value(salary)
over(partition by dept order by salary desc)-salary “ salary diff ”
from test;

 person   |    dept    | salary | diff by 1st record
-----------+------------+--------+-----------------------
phoebe    | accounting |   3700 |        0
lincoln   | accounting |   2950 |      750
rachel    | accounting |   2950 |      750
benjamin  | accounting |   2100 |     1600
michael   | accounting |   1700 |     2000
paul      | accounting |   1650 |     2050
alexander | it         |   3700 |        0
lj        | it         |   3250 |      450
sara      | it         |   2600 |     1100
ross      | it         |   1250 |     2450
theodore  | management |   3250 |        0
joey      | management |   3250 |        0
monica    | management |   2950 |      300
chandler  | management |   2400 |      850
fernando  | marketing  |   1650 |        0
bradley   | marketing  |   1000 |      650
(16 rows)

in above example first_vlaue() function return first record from
the partition value after order by clause executed.
i.e in accounting partition in would return 3700 ( first after order by desc )
always, from there we could calculate difference in salary from top salary
of the partition.