Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [greatest-n-per-group]

Use this for problems that involve returning one or more rows from each group in a data set. Despite the name, this tag is suitable for minimum- and maximum- per group queries.

360 questions
105
votes
6 answers

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the number of rows to list might vary per group (defined by…
Paul White
  • 94,921
  • 30
  • 437
  • 687
84
votes
6 answers

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the first row for each Val, ordering by Kind. Sample…
BrunoLM
  • 3,533
  • 7
  • 28
  • 22
78
votes
6 answers

How do I efficiently get "the most recent corresponding row"?

I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the rows of another table. I have a table, inventory…
Tom Ellis
  • 1,639
  • 3
  • 16
  • 14
39
votes
5 answers

In PostgreSQL, is there a type-safe first() aggregate function?

I'm looking for a first() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement) RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT $1; $$; -- And then wrap…
Alexandre Neto
  • 577
  • 2
  • 5
  • 11
23
votes
2 answers

How to make DISTINCT ON faster in PostgreSQL?

I have a table station_logs in a PostgreSQL 9.6 database: Column | Type | ---------------+-----------------------------+ id | bigint | bigserial station_id | integer …
Kokizzu
  • 1,403
  • 6
  • 18
  • 35
21
votes
3 answers

Get rows with most recent date for each different item

Let's say this is the sample date coming from a join of 2 tables. Database is Postgres 9.6 id product_id invoice_id amount date 1 PROD1 INV01 2 01-01-2018 2 PROD2 INV02 3 01-01-2018 3 …
Alin
  • 325
  • 1
  • 2
  • 7
19
votes
3 answers

SQL Server - Select most recent record from each group when performance is critical

I run a SQL Server 2016 database where I have the following table with 100+ millions rows: StationId | ParameterId | DateTime | Value 1 | 2 | 2020-02-04 15:00:000 | 5.20 1 | 2 | 2020-02-04 14:00:000 |…
Pedro Ludovico Bozzini
  • 301
  • 1
  • 2
  • 6
17
votes
4 answers

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) speed | integer …
Feyd
  • 171
  • 1
  • 1
  • 5
17
votes
3 answers

Select which has max date or latest date

Here are two tables. SCHOOL_STAFF SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID ================================================================= ABE Principal 24-JAN-13 111222 ABE …
riz
  • 189
  • 1
  • 1
  • 5
17
votes
4 answers

How to select first row from a join that returns multple rows on the primary key

This is related to this question: Joining multiple tables results in duplicate rows I have two tables that I am joining. They share a key. The person table has one name per primary key but the email table has multiple emails per personId. I want…
normandantzig
  • 415
  • 2
  • 5
  • 11
16
votes
2 answers

SELECT DISTINCT ON, ordered by another column

Please consider the following table test: CREATE TABLE test(col1 int, col2 varchar, col3 date); INSERT INTO test VALUES (1,'abc','2015-09-10') , (1,'abc','2015-09-11') , (2,'xyz','2015-09-12') , (2,'xyz','2015-09-13') , (3,'tcs','2015-01-15') ,…
Luis
  • 347
  • 2
  • 3
  • 9
16
votes
6 answers

Get second highest value in a table

id value 1 50 2 60 3 55 select max(value) from tablename; Generally we know, we will get 60, but I need the next value 55. How do I get the value 55 using SQL?
Mahfuz Morshed
  • 257
  • 2
  • 3
  • 10
15
votes
2 answers

SELECT LIMIT 1 per column value?

Lets say I have the following table user_id comment 2 thats cool 2 awesome 3 i hate this 3 okay 6 this is weird 6 hello? 6 what is it 9 how are you 16 too slow 16 yes 17 alrighty How can you select one row per…
Jake Wilson
  • 2,487
  • 8
  • 22
  • 23
14
votes
1 answer

get only rows with max group value

for example, we have: element | group_value | value a | 1 | 2000 a | 2 | 1500 a | 2 | 2500 b | 1 | 1000 I'd like to return only last 3 records as those are the records with max group…
spuppis
  • 153
  • 1
  • 1
  • 5
13
votes
1 answer

Is it possible to select distinct values in a json document using PostgreSQL?

I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents { id: 1, s: "foo" }, { id:2, s: "bar" }, { id:3, s:…
Trasplazio Garzuglio
  • 233
  • 1
  • 2
  • 6
1
2 3
…
23 24