Stack Overflow
Stack Overflow

Questions tagged [greatest-n-per-group]

Query the row with the greatest/least value per group.

One of the most frequent stumpers for SQL programmers is to query rows that match the greatest (or least) value over a given group. For example, all details about the City with the largest population per state. You can't do it simply with GROUP BY because the other columns will be ambiguous.

A variation that appears occasionally is to query the top n rows from each group (see the ).

Make sure to include the database tags as well (, , etc.) because depending on the database, the optimal query might be different.

4224 questions
168
votes
14 answers

Select row with most recent date per user

I have a table ("lms_attendance") of users' check-in and out times that looks like this: id user time io (enum) 1 9 1370931202 out 2 9 1370931664 out 3 6 1370932128 out 4 12 1370932128 out 5 12 1370933037 in I'm trying…
Keith
  • 4,144
  • 7
  • 25
  • 43
162
votes
6 answers

How to select the rows with maximum values in each group with dplyr?

I would like to select a row with maximum value in each group with dplyr. Firstly I generate some random data to show my question set.seed(1) df <- expand.grid(list(A = 1:5, B = 1:5, C = 1:5)) df$value <- runif(nrow(df)) In plyr, I could use a…
Bangyou
  • 9,462
  • 16
  • 62
  • 94
154
votes
7 answers

How to select only the first rows for each unique value of a column?

Let's say I have a table of customer addresses: +-----------------------+------------------------+ | CName | AddressLine | +-----------------------+------------------------+ | John Smith | 123 Nowheresville …
nuit9
  • 1,633
  • 3
  • 14
  • 8
146
votes
5 answers

Postgresql extract last row for each id

Suppose I've next data id date another_info 1 2014-02-01 kjkj 1 2014-03-11 ajskj 1 2014-05-13 kgfd 2 2014-02-01 SADA 3 2014-02-01 sfdg 3 2014-06-12 …
Marta
  • 3,493
  • 6
  • 28
  • 43
143
votes
9 answers

How can I select rows with most recent timestamp for each key value?

I have a table of sensor data. Each row has a sensor id, a timestamp, and other fields. I want to select a single row with latest timestamp for each sensor, including some of the other fields. I thought that the solution would be to group by sensor…
franklynd
  • 1,850
  • 3
  • 13
  • 11
114
votes
3 answers

How to select id with max date group by category in PostgreSQL?

For an example, I would like to select id with max date group by category, the result is: 7, 2, 6 id category date 1 a 2013-01-01 2 b 2013-01-03 3 c 2013-01-02 4 a 2013-01-02 5 b 2013-01-02 6 c …
user2412043
  • 1,165
  • 2
  • 8
  • 5
113
votes
7 answers

Get most recent row for given ID

In the table below, how do I get just the most recent row with id=1 based on the signin column, and not all 3 rows? +----+---------------------+---------+ | id | signin | signout | +----+---------------------+---------+ | 1 |…
enchance
  • 29,075
  • 35
  • 87
  • 127
99
votes
2 answers

Get records with highest/smallest per group

How can one get records with highest/smallest per group? Former title of this question was "using rank (@Rank := @Rank + 1) in complex query with subqueries - will it work?" because I was looking for solution using ranks, but now I see that the…
Tomas
  • 57,621
  • 49
  • 238
  • 373
98
votes
12 answers

Can I do a max(count(*)) in SQL?

Here's my code: select yr,count(*) from movie join casting on casting.movieid=movie.id join actor on casting.actorid = actor.id where actor.name = 'John Travolta' group by yr; Here's the question: Which were the busiest years for 'John Travolta'.…
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
91
votes
5 answers

Select info from table where row has max date

My table looks something like this: group date cash checks 1 1/1/2013 0 0 2 1/1/2013 0 800 1 1/3/2013 0 700 3 1/1/2013 0 600 1 1/2/2013 0 400 3 1/5/2013 0 …
kqlambert
  • 2,693
  • 6
  • 33
  • 50
91
votes
7 answers

Select a Column in SQL not in Group By

I have been trying to find some info on how to select a non-aggregate column that is not contained in the Group By statement in SQL, but nothing I've found so far seems to answer my question. I have a table with three columns that I want from it.…
user1603734
  • 1,071
  • 2
  • 10
  • 15
88
votes
7 answers

SQL Left Join first match only

I have a query against a large number of big tables (rows and columns) with a number of joins, however one of tables has some duplicate rows of data causing issues for my query. Since this is a read only realtime feed from another department I can't…
Dave
  • 1,823
  • 2
  • 16
  • 26
85
votes
3 answers

Optimize GROUP BY query to retrieve latest row per user

I have the following log table for user messages (simplified form) in Postgres 9.2: CREATE TABLE log ( log_date DATE, user_id INTEGER, payload INTEGER ); It contains up to one record per user and per day. There will be approximately…
xpapad
  • 4,376
  • 1
  • 24
  • 25
84
votes
7 answers

Get the latest date from grouped MySQL data

I have the following data in my database: |NO | model | date | +---+-------+----------+ |1 | bee |2011-12-01| |2 | bee |2011-12-05| |3 | bee |2011-12-12| |4 | tar |2011-12-13| I want to get the latest date of each model group: |…
nunu
  • 2,703
  • 9
  • 33
  • 55
84
votes
7 answers

Django Query That Get Most Recent Objects From Different Categories

I have two models A and B. All B objects have a foreign key to an A object. Given a set of A objects, is there anyway to use the ORM to get a set of B objects containing the most recent object created for each A object. Here's an simplified…
Zach
  • 18,594
  • 18
  • 59
  • 68
1
2
3
…
99 100