Stack Exchange
Use OVER..PARTITION BY with distinct value
Table CALENDAR_ORG has the following column and data
BUSINESS DAY - 20150801  20150801   20150801   20150801
UNIT_CODE    - HK        IN         HK         IN
GROUP_NAME   - ANG       ANG        ANG        ANG

I would like to use OVER..PARTION BY to select max BUSINESS_DAY categorised by UNIT_CODE
If using GROUP BY, it will be
SELECT MAX(BUSINESS_DAY) AS RUN_DATE FROM CALENDAR_ORG WHERE GROUP_NAME ='ANG' GROUP BY UNIT_CODE;
return:
20150801
20150801
Indeed, I am getting 4 records of 20150801 using below query with OVER..PARTITION BY
SELECT MAX(BUSINESS_DAY) OVER (PARTITION BY UNIT_CODE) AS RUN_DATE FROM CALENDAR_ORG WHERE GROUP_NAME='ANG';
20150801
20150801
20150801
20150801
The expected result should be 
20150801
20150801

Where is the question?

I would like  to achieve 2 records return using OVER..PARTITION BY. Date return should be categorised by UNIT_CODE, not depend on number of row of records.

Please edit the question - and bung in the desired results

Edited, with desired results

OVER() will not change the number of rows returned, only a GROUP BY will. You can use DISTINCT MAX(BUSINESS_DAY)

@Maheswaran Ravisankar DISTINCT (MAX(BUSINESS_DAY) OVER (PARTITION BY UNIT_CODE) ? it will only return 1 row

Ofcourse add the UNIT_CODE to select as well

I just want  BUSINESS_DAY to be selected

it's ok..i can have both columns selected, but only BUSINESS_DAY is used for subsequent action

Problem solved
SELECT DISTINCT (MAX(BUSINESS_DAY) OVER (PARTITION BY UNIT_CODE)) AS RUN_DATE, UNIT_CODE FROM CALENDAR_ORG 
WHERE SGROUP_NAME='ANG';
