CODEWITHSUNDEEP
sqlsqlite
Fatih Fatih
Fatih Fatih

Reputation: 13

Getting rows that has max value

For example, my table like that;

name   count
----   ----
red      5
green    3
yellow   4
brown    5
purple   2
black    5

I want to get a result just like that;

name   count
----    ----
black    5
brown    5
red      5

So i want the rows that has max count. How can i do that ? The MAX() is giving only one row.

Upvotes: 0

Views: 74

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use dense_rank() window funtion

select * from 
(
select *, dense_rank() over(order by count desc) rn from table
) t where t.rn=1

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below -

select * from tablename
where count=(select max(count) from tablename)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

One way uses a subquery to find the max value, and then restricts by that max value:

SELECT name, count
FROM yourTable
WHERE count = (SELECT MAX(count) FROM yourTable);

Depending on your version of SQLite, you may also be able to use analytic functions here. From the documentation:

Window function support was added to SQLite with release version 3.25.0 (2018-09-15)

If your version supports it, we can try using RANK:

WITH cte AS (
    SELECT name, count, RANK() OVER (ORDER BY count DESC) rnk
    FROM yourTable
)

SELECT name, count
FROM cte
WHERE rnk = 1;

Upvotes: 2

Related Questions