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
1
vote
1 answer

Why does distinct on in subqueries hurt performance in PostgreSQL?

I've got a table users with fields id and email. id is the primary key and email is indexed as well. database> \d users +-----------------------------+-----------------------------+-----------------------------------------------------+ | Column …
The Hoff
  • 904
  • 1
  • 11
  • 22
1
vote
1 answer

PostgreSQL - sort by array_position and distinct on another column

I have a query which looks like this (actual query & tables are more complicated): SELECT item.other_uuid, item.uuid, category.name FROM item, category WHERE ... ORDER BY array_position(ARRAY[{'CDX', 'KDJ', 'PLM', 'OLA', 'OWK', 'CAT'}]::uuid[],…
François Constant
  • 5,531
  • 1
  • 33
  • 39
1
vote
1 answer

PostgreSQL DISTINCT ON

I am not able to define an alias or to choose specific columns, works only with wildcard: Working: SELECT DISTINCT ON (r.InsertDate) * FROM public.server AS s LEFT JOIN public.Report AS r ON s.Id = r.ServerId ORDER BY r.InsertDate DESC; Not…
Mdmr
  • 41
  • 4
1
vote
1 answer

GROUP BY and DISTINCT ON working differently for tables and views

I have a table Design and a view on that table called ArchivedDesign. The view is declared as: CREATE OR REPLACE VIEW public."ArchivedDesign" ("RootId", "Id", "Created", "CreatedBy", "Modified", "ModifiedBy", "VersionStatusId",…
Storm
  • 3,062
  • 4
  • 23
  • 54
1
vote
2 answers

Running "distinct on" across all unique thresholds in a postgres table

I have a Postgres 11 table called sample_a that looks like this: time | cat | val ------+-----+----- 1 | 1 | 5 1 | 2 | 4 2 | 1 | 6 3 | 1 | 9 4 | 3 | 2 I would like to create a query that for each unique…
Coder
  • 597
  • 7
  • 22
1
vote
0 answers

Index that DISTINCT ON would use in PostgreSQL

There are 2 tables: challenge entry (many entries per each challenge) To enter a challenge, one would enter their Email and Name (for the leaderboard) - there is no registration / authentication process, so email and leaderboard_name are both…
Meglio
  • 1,646
  • 2
  • 17
  • 33
1
vote
2 answers

PostgreSQL: Grouping then filtering table, with condition for nonexistence

In PostgreSQL, I have a table that, abstractly, looks like this: ╔═══╦═══╦═══╦═══╗ ║ A ║ B ║ C ║ D ║ ╠═══╬═══╬═══╬═══╣ ║ x ║ 0 ║ y ║ 0 ║ ║ x ║ 0 ║ x ║ 1 ║ ║ x ║ 1 ║ y ║ 0 ║ ║ x ║ 1 ║ z ║ 1 ║ ║ y ║ 0 ║ z ║ 0 ║ ║ y ║ 0 ║ x ║ 0 ║ ║ y ║ 1 ║ y ║ 0…
jschoi
  • 1,884
  • 1
  • 12
  • 26
1
vote
1 answer

DISTINCT ON (col) col_alias – alias not working

I am trying to run this command: SELECT DISTINCT ON (_id) test FROM creator_map.infos; which is giving me the error: ERROR: column "test" does not exist but according to the following tutorial, the test should be an alias SELECT DISTINCT ON …
A. L
  • 11,695
  • 23
  • 85
  • 163
1
vote
1 answer

Join on multiple tables using distinct on

create table emp ( emp_id serial primary key, emp_no integer, emp_ref_no character varying(15), emp_class character varying(15) ); create table emp_detail ( emp_detail_id serial primary key, emp_id integer, class_no integer, …
Nik
  • 204
  • 1
  • 7
  • 18
1
vote
3 answers

Postgres : Need distinct records count

I have a table with duplicate entries and the objective is to get the distinct entries based on the latest time stamp. In my case 'serial_no' will have duplicate entries but I select unique entries based on the latest time stamp. Below query is…
User850309
  • 61
  • 2
  • 8
1
vote
0 answers

Distinct on column random values

I need to fetch 12 questions from my table. I have a field called "bucket" which might have duplicate values. While fetching, I need only unique bucket values to be fetched and the number of rows has to be 12. This is my query: select * from ( …
Ankita Gupta
  • 155
  • 2
  • 14
1
vote
2 answers

Select first event after a timestamp per row in another table in PostgreSQL

I have a table with visits to some city by some person on some timestamp: city_visits: person_id city timestamp ----------------------------------------------- 1 Paris 2017-01-01 00:00:00 1 …
Jivan
  • 21,522
  • 15
  • 80
  • 131
1
vote
1 answer

Prepend DISTINCT ON in select statement in an active record scope

I am trying to use distinct on in rails with a scope, I've created a method in my model like this: def self.fetch_most_recent_by_user(scope) scope.where(guid: scope.except(:select).select("DISTINCT ON (eld_logs.user_id) user_id,…
Ashish Gaur
  • 2,030
  • 2
  • 18
  • 32
1
vote
2 answers

"distinct on" with group by postgres

I have the following records: id run_hour performance_hour value 2 "2017-06-25 09:00:00" "2017-06-25 07:00:00" 6 2 "2017-06-25 09:00:00" "2017-06-25 08:00:00" 5 1 "2017-06-25 09:00:00" "2017-06-25 08:00:00" 5 2 …
Dejell
  • 13,947
  • 40
  • 146
  • 229
1
vote
1 answer

SQL how can I use distinct on with the count function inside a join

I am new to sql and am using postgres 9.6, I have 2 queries and I would like to join them into 1 . I want to use the Count function and order by a field in descending order which I have done here 1st query select count(s.city)::text as…
Rome Torres
  • 1,061
  • 2
  • 13
  • 27
1 2 3
4
5 6 7