Skip to content
Advertisement

How to limit the no of results to just 5 per ‘user’ but allow for more rows if the immediate value at 5th row is same as the consecutive rows?

I have a database with Domain_id, salary and salary date of an organisation. My question is i have to find 5 highest salaries per domain, but the no of rows (5 rows) can increase to more, if the immediate 6th,7th or more rows have the same value as the 5th row. I tried using window function.

select * from (SELECT id_domain, salary_value,salary_date, dense_rank() OVER ( PARTITION BY id_domain ORDER BY salary_date DESC,salary_value desc ) AS comment_rank from employee) employee where comment_rank <=5

I tried using lag and lead in this context, still couldn’t figure it out

Advertisement

Answer

Use rank(). If you want 5+ then:

or:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement