Stack Overflow
Stack Overflow

Questions tagged [limit-per-group]

Query X rows with the greatest/least value per group, i.e. do the SQL LIMIT, but per group. This complex problem is more general variant of [tag:greatest-n-per-group].

Query X rows with the greatest/least value per group, i.e. do the SQL LIMIT, but per group. This complex problem is more general variant of .

22 questions
24
votes
3 answers

How to select the first N rows of each group?

I have two SQLite tables like this: AuthorId | AuthorName ---------------------- 1 | Alice 2 | Bob 3 | Carol ... | .... BookId | AuthorId | Title ---------------------------------- 1 | 1 | aaa1 2 …
Vladislav
  • 165
  • 1
  • 5
  • 15
11
votes
5 answers

Average of latest N records per group

My current application calculates a point average based on all records for each user: SELECT `user_id`, AVG(`points`) AS pts FROM `players` WHERE `points` != 0 GROUP BY `user_id` The business requirement has changed and I need to calculate the…
JV-
  • 406
  • 1
  • 4
  • 11
6
votes
1 answer

SQLite: return only top 2 results within each group

I checked other solutions to similar problems, but sqlite does not support row_number() and rank() functions or there are no examples which involve joining multiple tables, grouping them by multiple columns and returning only top N results for each…
Superbman
  • 787
  • 1
  • 8
  • 24
5
votes
3 answers

Limit each group in group by

Now I understand that this has been asked several times before, but I have tried to apply different existing solutions to my specific problems for quite a while without success. So I turn here in hope of some guidance. I have a table called…
Adam
  • 115
  • 8
1
vote
1 answer

MySQL: How to SELECT only three of each differing x-column-name

I want to display my blogs in a very future proof way. So I need to make a query that will reference new blog types if they are created. Hence the x-column-name. In this case that's blogType. This select query should contain the blog information for…
Daniel J. Dunevant
  • 23
  • 9
1
vote
2 answers

SQL - Select top n grouped by multiple fields, ordered by count

I am attempting to find the top n records when grouped by multiple attributes. I believe it is related to this problem, but I am having difficulty adapting the solution described to my situation. To simplify, I have a table with columns (did is…
Cyruno
  • 13
  • 4
1
vote
1 answer

sum of the three largest values in a column, per group

I ran in to this problem on a CS question site, and I can't figure out how to do this. Problem My first solution ran in to a dead end with this code SELECT recipient FROM transfers GROUP BY recipient HAVING sum(amount) >= 1024 ORDER BY …
Jonathan
  • 53
  • 1
  • 1
  • 6
1
vote
2 answers

How to limit results of SQLite per specific group of results?

I have the following problem at work. I have a large table with different columns and few 100 000s of rows. I'll only post the ones im interested in. Assume the following data set Device ID, Feature Id, Feature Status 1, 1, 0 1, …
Alexandru Lucian Susma
  • 199
  • 2
  • 10
1
vote
3 answers

SQL : max occurence(s) for each value

I have a very simple table (LOG) containing attributes MAC_ADDR, IP_SRC, IP_DST, URL, PROTOCOL. I want the first n lines containing IP_SRC, URL, #OfOccurrences ordered by decreasing #OfOccurrences for each IP_SRC in my table when PROTOCOL='DNS'. To…
thefiercerabbit
  • 33
  • 3
1
vote
3 answers

How to select first N winners of each team from SQLITE?

I have a sqlite3 table like this: sqlite> select * from scores; team Name score ---------- --------- ---------- A Name1 93 A Name2 96 A Name3 78 A Name4 82 B …
user2545464
  • 191
  • 1
  • 8
1
vote
0 answers

Android - SQLite Select the first N occurrences of each differing column value

Suppose I have a SQL table "songs" with three columns: "title", "artist" and "ranking": track | artist | ranking ---------+--------+--------- A | A1 | 3 B | A1 | 2 C | A1 | 1 D | A2 | 4 …
Luke Beveridge
  • 505
  • 1
  • 6
  • 19
1
vote
0 answers

Laravel: hasMany() with take() only working on first result

I have the following models. class User extends Eloquent { public function comments() { return $this->hasMany('Comment'); } } class Comment extends Eloquent { public function user() { return $this->belongsTo('User'); } } For the…
Don Boots
  • 2,028
  • 2
  • 18
  • 26
1
vote
0 answers

SQLite - Top 10 of 5 different groups sorted by length

I read many questions with the greatest-n-per-group tag, but I can't find a better solution than the UNION ALL trick for this SQL query : select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as…
pihug12
  • 317
  • 3
  • 14
0
votes
1 answer

How can I display the top 5 artists by sum of play count for each year?

I am working on a project using my daily music listens data from Apple Music from 2018-2022. The table daily_listens consists of the following columns shown in this…
Max
  • 1
  • 1
0
votes
1 answer

SQL Oracle - 50 records for each agent for previous month

I've a table (processed_amounts) which shows 'amount', 'date_processed', and the 'agent' which processed the amount. I need a query which will pull through a specified number of processed entries, for instance, 50 processed entries, for a specified…
ex-patriot
  • 97
  • 2
  • 11
1
2