CODEWITHSUNDEEP
sqloracle-databaseoracle11g
LeonardBet
LeonardBet

Reputation: 19

Retrieve rows with highest salary per month

I need to do this but I don't know as. I have this table.

USER | SALARY  |   DATE
1234   1881,33     01/01/08
8762   2578        01/01/08
8726   2183,6475   01/01/08
2321   1745,8525   01/01/08
3123   1639,2      01/01/08
1934   2572        01/01/08

Is it possible to select from months of all years the person that has the bigger salary? In this case January 2008 was 8762.

Upvotes: 0

Views: 1684

Answers (3)

Rajballam Singh
Rajballam Singh

Reputation: 1

This query executed in mysql workbench..and worked as expected

select 
    DATE_FORMAT(`date_field`,'%Y-%m') AS date_field,
    max(salary) as salary
from
    employees
group by
    date_field;

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

This can be achieved using window function ROW_NUMBER.

It's used to enumerate rows in different groups ( in your case months - explanation below) ordered by salary in descending manner (in your case highest salary will be assigned a rownum = 1 ):

SELECT 
 to_char(date_column, 'YYYY-MM'), 
 user
FROM (
 SELECT
 *,
 row_number() OVER (PARTITION BY to_char(date_column, 'YYYY-MM') ORDER BY salary DESC) AS rownum
FROM yourtable
 ) foo
WHERE rownum = 1
ORDER BY 1 -- not needed, looks nice in the results

Using TO_CHAR function to get only the year and month part from your date and get the groups:

TO_CHAR(date_column, 'YYYY-MM')

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

I think yo can use a in clause for subselect with group by

select user from my_table 
where (salary, to_char(date,'mm-yyyy')) in 
   select(max(salary), to_char(date,'mm-yyyy') month_year 
        from my_table group by month_year);

Upvotes: 2

Related Questions