Search code examples
sqlitelimit

More than one result on request with limit 1 sqllite

The result is one line, but there are several people with the same value of the bonus field

SELECT Name, bonus
From employees
ORDER by bonus
Limit 1

result

Ivan 100

but it is required that it was

Ivan 100 Petr 100

did this, but it seems very confusing to me:

SELECT Name, bonus
From employees
Where bonus= (SELECT id From employees ORDER by bonus Limit 1)
Solution

  • In SQLite you can achieve that by using RANK or dense_rank window function

    select name, bonus 
  from (select name, bonus, dense_rank() over(order by bonus desc) dns_rnk from employees) 
 where dns_rnk = 1;

    In sub-query it will rank employees on bonus and the outer query will filter out unneeded lines.