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

Selecting rows ordered by some column and distinct on another

Related to - PostgreSQL DISTINCT ON with different ORDER BY I have table purchases (product_id, purchased_at, address_id) Sample data: | id | product_id | purchased_at | address_id | | 1 | 2 | 20 Mar 2012 21:01 | 1 | | 2 | …
sl_bug
  • 5,066
  • 5
  • 21
  • 22
42
votes
3 answers

Select row by max value in group in a pandas dataframe

I have a dataframe which was created via a df.pivot: type start end F_Type to_date A 20150908143000 345 316 B 20150908140300 NaN 480 …
user636322
  • 1,151
  • 3
  • 12
  • 23
39
votes
7 answers

Selecting most recent and specific version in each group of records, for multiple groups

The problem: I have a table that records data rows in foo. Each time the row is updated, a new row is inserted along with a revision number. The table looks like: id rev field 1 1 test1 2 1 fsdfs 3 1 jfds 1 2 test2 Note: the last…
orange
  • 7,755
  • 14
  • 75
  • 139
39
votes
8 answers

How to SELECT the newest four items per category?

I have a database of items. Each item is categorized with a category ID from a category table. I am trying to create a page that lists every category, and underneath each category I want to show the 4 newest items in that category. For…
justinl
  • 10,448
  • 21
  • 70
  • 88
38
votes
5 answers

Select rows based on last date

I have a table named Course in a Postgres database: How can I select rows which have course name with latest date? I mean if I have two same course names for one ID, I should only show the latest one as the below result. Simply, I want only to show…
Aan
  • 12,247
  • 36
  • 89
  • 150
34
votes
5 answers

Mysql select distinct

I am trying to select of the duplicate rows in mysql table it's working fine for me but the problem is that it is not letting me select all the fields in that query , just letting me select the field name i used as distinct , lemme write the query…
Shanon
  • 353
  • 1
  • 3
  • 4
34
votes
9 answers

Get most common value for each value of another column in SQL

I have a table like this: Column | Type | Modifiers ---------+------+----------- country | text | food_id | int | eaten | date | And for each country, I want to get the food that is eaten most often. The best I can think of (I'm using…
Martin C. Martin
  • 341
  • 1
  • 3
  • 5
30
votes
10 answers

How to get the last record per group in SQL

I am facing a rather interesting problem. I have a table with the following structure: CREATE TABLE [dbo].[Event] ( Id int IDENTITY(1,1) NOT NULL, ApplicationId nvarchar(32) NOT NULL, Name nvarchar(128) NOT NULL, Description…
bleepzter
  • 9,607
  • 11
  • 41
  • 64
29
votes
7 answers

SQL: how to limit a join on the first found row?

How to make a join between two tables but limiting to the first row that meets the join condition ? In this simple example, I would like to get for every row in table_A the first row from table_B that satisfies the condition : select table_A.id,…
kkung
  • 715
  • 4
  • 10
  • 18
27
votes
1 answer

GROUP BY in Postgres - no equality for JSON data type?

I have the following data in a matches…
janderson
  • 963
  • 4
  • 14
  • 26
26
votes
8 answers

How to select the most recent set of dated records from a mysql table

I am storing the response to various rpc calls in a mysql table with the following fields: Table: rpc_responses timestamp (date) method (varchar) id (varchar) response (mediumtext) PRIMARY KEY(timestamp,method,id) What is the…
Ken
  • 77,016
  • 30
  • 84
  • 101
26
votes
3 answers

SQL: Find the max record per group

Possible Duplicate: Retrieving the last record in each group I have one table, which has three fields and data. Name , Top , Total cat , 1 , 10 dog , 2 , 7 cat , 3 , 20 horse , 4 , 4 cat , 5 , 10 dog , 6 ,…
user319088
  • 261
  • 1
  • 3
  • 3
26
votes
8 answers

In SQL, how to select the top 2 rows for each group

I have a table as following: NAME SCORE ----------------- willy 1 willy 2 willy 3 zoe 4 zoe 5 zoe 6 Here's the sample The aggregation function for group by only allow me to get the highest score for each…
waitingkuo
  • 89,478
  • 28
  • 112
  • 118
25
votes
2 answers

Oracle SQL query: Retrieve latest values per group based on time

I have the following table in an Oracle DB id date quantity 1 2010-01-04 11:00 152 2 2010-01-04 11:00 210 1 2010-01-04 10:45 132 2 2010-01-04 10:45 318 4 2010-01-04 10:45 122 1 2010-01-04 10:30 …
Tom
  • 1,713
  • 5
  • 19
  • 24
24
votes
8 answers

How do I limit a LEFT JOIN to the 1st result in SQL Server?

I have a bit of SQL that is almost doing what I want it to do. I'm working with three tables, a Users, UserPhoneNumbers and UserPhoneNumberTypes. I'm trying to get a list of users with their phone numbers for an export. The database itself is old…
Justin808
  • 20,859
  • 46
  • 160
  • 265
1 2 3
…
99 100