Questions tagged [greatest-n-per-group]
Query the row with the greatest/least value per group.
3,917
questions
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 ...
- 138k
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 ...
- 27.6k
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 ...
- 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
...
- 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| ...
- 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? (...
- 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
...
- 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 ...
- 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,...
- 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
...
- 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 ...
- 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 ...
- 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 ...
- 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:...
- 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 ...
- 157k