Select a column with condition with other column as it is

Question

I have not searched a lot before asking because I am feeling the search string complicated to write.

I will ask by example instead of description.

I have a table called user_sale

id emp_id emp_name emp_location date sales
------------------------------------------------------
1  111    mr.one   A            2013/07/17   5000
2  111    mr.one   C            2013/07/14   6000
3  222    mr.two   B            2013/06/15   5500

and so on.

In output I want all field as it is but want emp_location latest within a month. I am able to get month and year from date. So I can do group by year and month.

expected output:

id emp_id emp_name emp_location date sales
------------------------------------------------------
1  111    mr.one   A            2013/07/17   5000
2  111    mr.one   A            2013/07/14   6000
3  222    mr.two   B            2013/06/15   5500

One solution is to join with the same table, but since the table contains large data it does not seem like a proper solution.

Answer 1

Use the window function first_value() to get the "first" of one column (emp_location) as defined by another column (date), embedded in otherwise unchanged rows:

SELECT id, emp_id, emp_name
     , first_value(emp_location) OVER (PARTITION BY emp_id
                                       ORDER BY date DESC) AS emp_location
     , date, sales
FROM   user_sale
ORDER  BY id;

Assuming that emp_id is unique per group as you define it.

Aside: you shouldn't be using date (reserved word in SQL standard) or id (non-descriptive) as column names.