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
83
votes
4 answers

GROUP BY having MAX date

I have problem when executing this code: SELECT * FROM tblpm n WHERE date_updated=(SELECT MAX(date_updated) FROM tblpm GROUP BY control_number HAVING control_number=n.control_number) Basically, I want to return the most recent date for each…
J-J
  • 1,063
  • 5
  • 22
  • 47
81
votes
11 answers

SQL query to select distinct row with minimum value

I want an SQL statement to get the row with a minimum value. Consider this table: id game point 1 x 5 1 z 4 2 y 6 3 x 2 3 y 5 3 z 8 How do I select the ids that have the minimum value in the point…
balaji
  • 1,236
  • 2
  • 18
  • 28
76
votes
2 answers

PostgreSQL MAX and GROUP BY

I have a table with id, year and count. I want to get the MAX(count) for each id and keep the year when it happens, so I make this query: SELECT id, year, MAX(count) FROM table GROUP BY id; Unfortunately, it gives me an error: ERROR: column…
Project Dumbo Dev
  • 763
  • 1
  • 5
  • 5
72
votes
1 answer

Subset rows corresponding to max value by group using data.table

Assume I have a data.table containing some baseball players: library(plyr) library(data.table) bdt <- as.data.table(baseball) For each group (given by player 'id'), I want to select rows corresponding to the maximum number of games 'g'. This is…
hadley
  • 102,019
  • 32
  • 183
  • 245
70
votes
6 answers

How to get the latest record in each group using GROUP BY?

Let's say I have a table called messages with the columns: id | from_id | to_id | subject | message | timestamp I want to get the latest message from each user only, like you would see in your FaceBook inbox before you drill down into the actual…
user1019144
  • 1,203
  • 2
  • 14
  • 19
67
votes
6 answers

SQL query to get most recent row for each instance of a given key

I'm trying to get the ip, user, and most recent timestamp from a table which may contain both the current ip for a user and one or more prior ips. I'd like one row for each user containing the most recent ip and the associated timestamp. So if a…
alanc10n
  • 4,897
  • 7
  • 36
  • 41
66
votes
2 answers

FORCE INDEX in MySQL - where do I put it?

I have the following MySQL query that works perfectly fine. Except that I need to add a FORCE INDEX and I'm unsure on where I have to do this. I tried just about every location and always receive a MySQL error. What am I doing wrong? Here is the…
user2643870
  • 965
  • 1
  • 10
  • 19
64
votes
8 answers

Using ORDER BY and GROUP BY together

My table looks like this (and I'm using MySQL): m_id | v_id | timestamp ------------------------ 6 | 1 | 1333635317 34 | 1 | 1333635323 34 | 1 | 1333635336 6 | 1 | 1333635343 6 | 1 | 1333635349 My target is to take each…
Luis
  • 3,257
  • 13
  • 50
  • 59
60
votes
5 answers

how to sort order of LEFT JOIN in SQL query?

OK I tried googling for an answer like crazy, but I couldn't resolve this, so I hope someone will be able to help. Let's say I have a table of users, very simple table: id | userName 3 Michael 4 Mike 5 George and I have another table of…
Frantisek
  • 7,485
  • 15
  • 59
  • 102
56
votes
8 answers

Aggregate a dataframe on a given column and display another column

I have a dataframe in R of the following form: > head(data) Group Score Info 1 1 1 a 2 1 2 b 3 1 3 c 4 2 4 d 5 2 3 e 6 2 1 f I would like to aggregate it following the Score column…
jul635
  • 794
  • 1
  • 7
  • 13
56
votes
3 answers

Sorting columns and selecting top n rows in each group pandas dataframe

I have a dataframe like this: mainid pidx pidy score 1 a b 2 1 a c 5 1 c a 7 1 c b 2 1 a e 8 2 x y 1 2 y z 3 …
Shubham R
  • 7,382
  • 18
  • 53
  • 119
55
votes
8 answers

T-SQL: Selecting Column Based on MAX(Other Column)

I'm hoping there's a simple way to do this without using a sub-query: Scenario: You have "TableA" with columns "Key", "SubKey", and "Value". I need to get the "Value" of the MAX("SubKey") for a given "Key". So if the Table contained the rows: KEY…
John
  • 17,163
  • 16
  • 65
  • 83
54
votes
8 answers

How to select data where a field has a min value in MySQL?

I want to select data from a table in MySQL where a specific field has the minimum value, I've tried this: SELECT * FROM pieces WHERE MIN(price) Please any help?
Sami El Hilali
  • 981
  • 3
  • 12
  • 21
51
votes
7 answers

How do I join the most recent row in one table to another table?

I have data that looks like this: entities id name 1 Apple 2 Orange 3 Banana Periodically, a process will run and give a score to each entity. The process generates the data and adds it to a scores table like…
GloryFish
  • 13,078
  • 16
  • 53
  • 43
45
votes
8 answers

MAX function in where clause mysql

How can I use max() function in where clause of a mysql query, I am trying: select firstName,Lastname,MAX(id) as max where id=max; this is giving me an error: Unknown column 'max' in 'where clause'
user3176971
  • 599
  • 2
  • 6
  • 16
1 2
3
…
99 100