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
1949
votes
20 answers

Select first row in each GROUP BY group?

I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My…
David Wolever
  • 148,955
  • 89
  • 346
  • 502
1620
votes
27 answers

SQL select only rows with max value on a column

I have this table for documents (simplified version here): id rev content 1 1 ... 2 1 ... 1 2 ... 1 3 ... How do I select one row per id and only the greatest rev? With the above data, the result should contain two rows: [1, 3,…
Majid Fouladpour
  • 29,356
  • 21
  • 76
  • 127
1308
votes
34 answers

Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If…
Vijay Dev
  • 26,966
  • 21
  • 76
  • 96
908
votes
22 answers

How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?

I have a table of player performance: CREATE TABLE TopTen ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, home INT UNSIGNED NOT NULL, `datetime`DATETIME NOT NULL, player VARCHAR(6) NOT NULL, resource INT NOT NULL ); What query will return…
Kaptah
  • 9,785
  • 4
  • 22
  • 19
753
votes
19 answers

Get top 1 row of each group

I have a table which I want to get the latest entry for each group. Here's the table: DocumentStatusLogs Table |ID| DocumentID | Status | DateCreated | | 2| 1 | S1 | 7/29/2011 | | 3| 1 | S2 | 7/30/2011 | | 6| 1 …
kazinix
  • 28,987
  • 33
  • 107
  • 157
664
votes
35 answers

Fetch the rows which have the Max value for a column for each distinct value of another column

Table: UserId, Value, Date. I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. How do I do this in SQL? (Preferably Oracle.) I need to get ALL the UserIds. But for each…
Umang
  • 6,675
  • 3
  • 18
  • 6
520
votes
14 answers

Using LIMIT within GROUP BY to get N results per group?

The following query: SELECT year, id, rate FROM h WHERE year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) GROUP BY id, year ORDER BY id, rate DESC yields: year id rate 2006 p01 8 2003 p01 7.4 2008 p01 6.8 2001 p01…
Wells
  • 10,415
  • 14
  • 55
  • 85
465
votes
13 answers

SQL join: selecting the last records in a one-to-many relationship

Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one SELECT statement. What is the best practice? Any advice on building…
netvope
  • 7,647
  • 7
  • 32
  • 42
369
votes
23 answers

how do I query sql for a latest record date for each user

I have a table that is a collection entries as to when a user was logged on. username, date, value -------------------------- brad, 1/2/2010, 1.1 fred, 1/3/2010, 1.0 bob, 8/4/2009, 1.5 brad, 2/2/2010, 1.2 fred, …
fishhead
  • 5,829
  • 7
  • 32
  • 43
345
votes
19 answers

Get records with max value for each group of grouped SQL results

How do you get the rows that contain the max value for each grouped set? I've seen some overly-complicated variations on this question, and none with a good answer. I've tried to put together the simplest possible example: Given a table like that…
Yarin
  • 173,523
  • 149
  • 402
  • 512
331
votes
27 answers

ROW_NUMBER() in MySQL

Is there a nice way in MySQL to replicate the SQL Server function ROW_NUMBER()? For example: SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow FROM Table1 Then I could, for example, add a…
Paul
  • 16,285
  • 13
  • 41
  • 52
280
votes
15 answers

Select top 10 records for each category

I want to return top 10 records from each section in one query. Can anyone help with how to do it? Section is one of the columns in the table. Database is SQL Server 2005. I want to return the top 10 by date entered. Sections are business, local,…
jbcedge
  • 18,965
  • 28
  • 68
  • 89
258
votes
6 answers

Pandas get topmost n records within each group

Suppose I have pandas DataFrame like this: df = pd.DataFrame({'id':[1,1,1,2,2,2,2,3,4], 'value':[1,2,3,1,2,3,4,1,1]}) which looks like: id value 0 1 1 1 1 2 2 1 3 3 2 1 4 2 2 5 2 3 6 2 4 7 3 …
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
213
votes
6 answers

GROUP BY with MAX(DATE)

I'm trying to list the latest destination (MAX departure time) for each train in a table, for example: Train Dest Time 1 HK 10:00 1 SH 12:00 1 SZ 14:00 2 HK 13:00 2 SH …
Aries
  • 2,191
  • 2
  • 15
  • 7
189
votes
12 answers

Get top n records for each group of grouped results

The following is the simplest possible example, though any solution should be able to scale to however many n top results are needed: Given a table like that below, with person, group, and age columns, how would you get the 2 oldest people in each…
Yarin
  • 173,523
  • 149
  • 402
  • 512
1
2 3
…
99 100