CODEWITHSUNDEEP
sqlsqlite
user10907559
user10907559

Reputation:

How do I select rows with maximum value?

Given this table I want to retrieve for each different url the row with the maximum count. For this table the output should be: 'dell.html' 3, 'lenovo.html' 4, 'toshiba.html' 5 

+----------------+-------+
| url            | count |
+----------------+-------+
| 'dell.html'    |     1 |
| 'dell.html'    |     2 |
| 'dell.html'    |     3 |
| 'lenovo.html'  |     1 |
| 'lenovo.html'  |     2 |
| 'lenovo.html'  |     3 |
| 'lenovo.html'  |     4 |
| 'toshiba.html' |     1 |
| 'toshiba.html' |     2 |
| 'toshiba.html' |     3 |
| 'toshiba.html' |     4 |
| 'toshiba.html' |     5 |
+----------------+-------+

What SQL query do I need to write to do this?

Upvotes: 0

Views: 56

Answers (2)

Oskars
Oskars

Reputation: 64

Try to use this query:

select url, max(count) as count
from table_name
group by url;

Upvotes: 4

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

use aggregate function

  select max(count) ,url from table_name group by url

From your comments it seems you need corelated subquery

select t1.* from table_name t1
 where t1.count = (select max(count) from table_name t2 where t2.url=t1.url
                 )

If row_number support on yours sqllite version then you can write query like below

select * from 
(
select *,row_number() over(partition by url  order by count desc) rn
  from table_name
) a where a.rn=1

Upvotes: 1

Related Questions