Stack Overflow
Stack Overflow

Questions tagged [distinct-on]

Use this tag for questions relevant to DISTINCT ON, which is used to eliminate rows that match on all specified expressions.

From PostgreSQL: Documentation:

DISTINCT ON eliminates rows that match on all the specified expressions.

Consider using the , if needed, in your question.

95 questions
3
votes
0 answers

Performant KNN search by distinct category in PL/pgSQL

For my master thesis I am analyzing several algorithms that could be useful for a mobile service provider (test data sets are based on a mobile music school) to find the optimal teacher for a new student taking the locations of a teacher's existing…
lem_s
  • 31
  • 2
3
votes
1 answer

How to replace a DISTINCT ON with GROUP BY in PostgreSQL 9?

I have been using the DISTINCT ON predicate and have decided to replace it with GROUP BY, mainly because it "is not part of the SQL standard and is sometimes considered bad style because of the potentially indeterminate nature of its results". I am…
EnzoR
  • 3,107
  • 2
  • 22
  • 25
3
votes
2 answers

How to use "distinct on" with doctrine?

I try to use "distinct on" with doctrine but I get the following error: Error: Expected known function, got 'on' class Report extends EntityRepository { public function findForList() { $queryBuilder = $this->createQueryBuilder('r'); …
Alexandre
  • 3,088
  • 3
  • 34
  • 53
3
votes
3 answers

PostgreSQL - Finding the oldest record with a specific value

I have a document management system that records all historical events in a history table. I've been asked to be able to provide the oldest doc_id that has a status of 5 for a given client on a given date. The table looks something like this…
Adam
  • 31
  • 4
2
votes
2 answers

DISTINCT ON slow for 300000 rows

I have a table named assets. Here is the ddl: create table assets ( id bigint primary key, name varchar(255) not null, value double precision …
MehdiB
  • 870
  • 12
  • 34
2
votes
1 answer

PostgreSQL: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Let's say I have the following PostgreSQL table called products: CREATE TABLE IF NOT EXISTS mytable ( id serial NOT NULL PRIMARY KEY, label VARCHAR(50) NOT NULL, info jsonb NOT NULL, created_at timestamp NOT NULL DEFAULT…
Johnny Metz
  • 5,977
  • 18
  • 82
  • 146
2
votes
1 answer

SQL left Join with DISTINCT ON returns duplicate rows

I`m using node with mikro-orm v5 and postgres 14. My schema goes like this: - Business - Addresses[] - Sectors[] And I want to query the addresses by distance. The problem is that the return is two addresses with the same ID, one for each…
Diogo
  • 47
  • 5
2
votes
1 answer

Postgresql update 200 random records

I've got a query returning 200 random distinct foo from my Database. I want to update the notes field for those rows. select distinct on (foo, random()) * from test order by random(), foo limit 200 update test notes = 'Flag' order by random() limit…
ClaskoTheKnight
  • 55
  • 7
2
votes
1 answer

PostgreSQL SELECT result with a distinct ids that prefers specific values from another table

I have a some_dictionary table and a some_dictionary_language table with data in multiple languages(and a reference to some_dictionary). I need to get unique values on some_dictionary.id preferring the result for the given language and if not the…
bezdroznik
  • 23
  • 3
2
votes
2 answers

How to use DISTINCT ON but ORDER BY another expression?

The model Subscription has_many SubscriptionCart. A SubscriptionCart has a status and an authorized_at date. I need to pick the cart with the oldest authorized_at date from all the carts associated to a Subscription, and then I have to order all the…
pinkfloyd90
  • 588
  • 3
  • 17
2
votes
0 answers

DISTINCT ON in subquery by SQLAlchemy

I have a problem with query by ORM SQLAlchemy. I need to write subquery like this SELECT DISTINCT ON (e.type) e.type AS e_type, e.num AS e_num FROM e ORDER BY e.type by ORM SQLAlchemy, but when I've used .subquery().alias("q") in my query,…
Ilya Petrov
  • 23
  • 7
2
votes
1 answer

PostgreSQL: latest row in DISTINCT ON less performant than max row in GROUP BY

I have a situation that I would like to better understand: I've a table t with two rows and one index: CREATE TABLE t ( refid BIGINT NOT NULL, created TIMESTAMPTZ NOT NULL ); CREATE INDEX t_refid_created ON t…
Alechko
  • 1,406
  • 1
  • 13
  • 27
2
votes
3 answers

PostgreSQL Performance: Query to find stocks reaching 52 week highs (joining rows of max values)

I have a very simple database structure with "end of day" stock prices which look similar to: finalyzer_pricedata=> \d pdEndOfDayPricEentity Table "public.pdendofdaypriceentity" Column | Type | Collation | Nullable |…
Herbert Poul
  • 4,512
  • 2
  • 31
  • 48
2
votes
3 answers

POSTGRES min/least value

I have a postgres 9.3 table with two columns. The first column has times and the second has routes. A route may have multiple times. I want to list all routes with their most minimum times. My table: Times Routes 07:15:00 Route a 09:15:00 …
Ang
  • 153
  • 1
  • 2
  • 9
2
votes
3 answers

Postgres: Distinct but only for one column and latest one

I have the following original table What I want is to retrieve latest history_id with distinct new_id. As follow: I found this distinct_but_only_one_column but it only contains how to retrieve distinct one column. Please help me out.
additionster
  • 628
  • 4
  • 14
1
2
3 4 5 6 7