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
2 answers

Get distinct information across many fields some of which are NULL

I have a table with just over 65 million rows and 140 columns. The data comes from several sources and is submitted at least every month. I look for a quick way to grab specific fields from this data only where they are unique. Thing is, I want to…
lukik
  • 3,919
  • 6
  • 46
  • 89
1
vote
3 answers

Strange behaviour with a CTE involving two joins

This post has been totally rephrased in order to make the question more understandable. Settings PostgreSQL 9.5 running on Ubuntu Server 14.04 LTS. Data model I have dataset tables, where I store data separately (time series), all those tables must…
jlandercy
  • 7,183
  • 1
  • 39
  • 57
1
vote
1 answer

Select distinct on does not work properly rails 4.2.5

I have this two models: class Comment < ActiveRecord::Base belongs_to :post end class Post < ActiveRecord::Base include PgSearch has_many :comments, dependent: destroy pg_search_scope :search_tsv, against: [:name], …
kitz
  • 879
  • 2
  • 9
  • 24
1
vote
1 answer

PL/pgSQL : Does DISTINCT ON passes through with it's result onto next CTE?

My code looks like this (schema), because it's pretty huge: something AS ( SELECT DISTINCT ON (x1,x2,x3,x4) ... ), something2 AS (xx.*, ... FROM something xx LEFT JOIN ...), something3 AS (xx.*, ... FROM something2 xx LEFT JOIN ...) SELECT ... FROM…
Patrick
  • 165
  • 1
  • 4
  • 13
1
vote
4 answers

Select DISTINCT returning too many records

I have two tables: Products and Items. I want to select distinct items that belong to a product based on the condition column, sorted by price ASC. +-------------------+ | id | name | +-------------------+ | 1 | Mickey Mouse …
dee
  • 1,848
  • 1
  • 22
  • 34
0
votes
0 answers

Is this index for SELECT DISTINCT ON wrong? Why the Seq Scan is still in plans?

I have this table: CREATE TABLE public.player ( company_id character varying NOT NULL, id character varying NOT NULL, created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, updated_at timestamp with time zone, …
Fred Hors
  • 3,258
  • 3
  • 25
  • 71
0
votes
1 answer

how to apply distinct and group by in django or in postgres?

table production code part qty process_id 1 21 10 10 1 22 12 10 2 22 15 10 1 21 10 12 1 22 12 12 I have to extract data like based on process, every process has multiple part but I can't take every part's data, so that have to…
Noyon Islam
  • 23
  • 1
  • 5
0
votes
1 answer

How to aggregate multiple rows in nested SELECT query Postgresql

I am quite unfamiliar to complex SQL requests ... What I am trying to achieve is to display of map of active weather alerts on a leaflet map page. Started by filling a Postgis table with the RSS feed from national weather agencies, then create a…
Rafiosan
  • 13
  • 3
0
votes
1 answer

How to use DISTINCT ON in Laravel Eloquent

Here is my full SQL Statement: SELECT DISTINCT ON (hbg.group_id) hbg.group_id, hbg.id AS id, hbg.group_name, hbg.group_description, hbg.group_type_id, hbgt.group_type_name, hbg.category_id, …
user16409170
0
votes
1 answer

How to use multiple distinct on statements in Postgres while retaining the correct order

I have a table with weather forecasts in Postgres that looks like this Here, a wind and a solar forecast is published every 15 minutes for the same time. I wish to select the latest wind and solar forecast from this table using a distinct on()…
Wouter
  • 477
  • 2
  • 6
  • 8
0
votes
1 answer

Grafana PostgreSQL distinct on() with time series

I'm quite new to Grafana and Postgres and could use some help with this. I have a dataset in PostgreSQL with temperature forecasts. Mutiple forecasts are published at various points throughout the day (indicated by dump_date) for the same reference…
Wouter
  • 477
  • 2
  • 6
  • 8
0
votes
1 answer

Postgres distinct on with case

I have a query where I am using CASE statement inside DISTINCT ON like this: SELECT DISTINCT ON(COALESCE(users.cuid,events.uid), CASE WHEN events.props->>'prop' IS NULL THEN '$none' WHEN events.props->>'prop' = '' THEN '$none' ELSE…
pratpor
  • 1,954
  • 1
  • 27
  • 46
0
votes
0 answers

Postgresql - Select distinct on limit 100

There's probably a simple solution to this. However my question is how do i go about selecting say 100 or 1000 distinct avclassfamily values in the following query? Ideally there would be a command that'd be something like 'select distinct ON (1000)…
dipl0
  • 1,017
  • 2
  • 13
  • 36
0
votes
1 answer

Remove Duplicate Result on Query

could help me solve this duplication problem where it returns more than 1 result for the same record I want to bring only 1 result for each id, and only the last history of each record. My Query: SELECT DISTINCT…
codevb
  • 83
  • 1
  • 7
0
votes
3 answers

Inner joins returning duplicate records

I write this query to retrieve data from 3 different tables. Here [all columns i selected are from the first and the second table], data is retrieved as i actually need : SELECT DISTINCT SD.salary_component,SS.posting_date,SS.payroll_entry, …
Mohamed Saif
  • 1
  • 2
1 2 3 4
5
6 7