[sql] OVER clause in Oracle

What is the meaning of the OVER clause in Oracle?

You can use it to transform some aggregate functions into analytic:

SELECT  MAX(date)
FROM    mytable

will return 1 row with a single maximum,

SELECT  MAX(date) OVER (ORDER BY id)
FROM    mytable

will return all rows with a running maximum.

It's part of the Oracle analytic functions.

Another way to use OVER is to have a result column in your select operate on another "partition", so to say.

This:

SELECT 
    name, 
    ssn, 
    case 
      when ( count(*) over (partition by ssn) ) > 1      
      then 1
      else 0
    end AS hasDuplicateSsn
FROM table;

returns 1 in hasDuplicateSsn for each row whose ssn is shared by another row. Great for making "tags" for data for different error reports and such.