Questions tagged [greatest-n-per-group]

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

Filter by
Sorted by
Tagged with
1730votes
20answers
1.5mviews

Select first row in each GROUP BY group?

As the title suggests, 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 ...
user avatar
1501votes
27answers
1.7mviews

SQL select only rows with max value on a column [duplicate]

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 ...
user avatar
1201votes
32answers
910kviews

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 ...
user avatar
  • 25.7k
866votes
21answers
1.8mviews

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

My table is: id home datetime player resource 1 10 04/03/2009 john 399 2 11 04/03/2009 juliet 244 5 12 04/03/2009 borat 555 3 10 03/03/2009 john 300 4 11 03/03/2009 juliet 200 6 12 03/03/2009 ...
user avatar
  • 9,323
675votes
22answers
798kviews

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| ...
user avatar
  • 27k
628votes
35answers
642kviews

Fetch the row which has the Max value for a 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. Is there a way to do this simply in SQL? (...
user avatar
  • 6,315
471votes
13answers
458kviews

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 ...
user avatar
  • 9,553
397votes
13answers
314kviews

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 ...
user avatar
  • 6,907
316votes
23answers
985kviews

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,...
user avatar
  • 5,269
314votes
25answers
656kviews

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 ...
user avatar
  • 15.2k
301votes
19answers
320kviews

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 ...
user avatar
  • 157k
248votes
14answers
576kviews

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 ...
user avatar
  • 17.7k
221votes
5answers
211kviews

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]}) >>> df id value 0 1 1 1 1 2 2 1 3 3 ...
user avatar
  • 98.5k
190votes
6answers
425kviews

GROUP BY with MAX(DATE) [duplicate]

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:...
user avatar
  • 1,961
177votes
12answers
206kviews

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 ...
user avatar
  • 157k

15 30 50 per page
1
2 3 4 5
262