How do I select rows with maximum value?

Question

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?

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Nick, Jan 13 '19 at 11:13
Note: `ROW_NUMBER` might be an option here, depending on your version of SQLite. — Tim Biegeleisen, Jan 13 '19 at 11:21

score 4 · Answer 1 · edited Jan 13 '19 at 13:07

4

Try to use this query:

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

edited Jan 13 '19 at 13:07

Gordon Linoff

1,242,037
58
646
786

answered Jan 13 '19 at 11:03

Oskars

64
3

Zaynul Abadin Tuhin · Accepted Answer · 2019-01-13T11:36:38.720

1

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

edited Jan 13 '19 at 11:36

answered Jan 13 '19 at 11:04

Zaynul Abadin Tuhin

31,407
5
33
63

My table actually has many more columns, I was doing a query similar to yours `select *, max(count) from table_name group by url`. But this outputs two count fields. Is there a way to make it just output all the columns in the table or is it fine to just ignore the extra column in the output? – Jan 13 '19 at 11:27
@elk yes there is a way but for that you have to share details table cause according to your question this is the answer – Zaynul Abadin Tuhin Jan 13 '19 at 11:33
Should have been more clear in my question but correlated subqueries was what I was looking for. – Jan 13 '19 at 11:53

How do I select rows with maximum value?

2 Answers2