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

Selecting the most recent, lowest price from multiple vendors for an inventory item

I’m fairly proficient at SQL, however this question has had myself stumped for quite a while now. In the most basic sense, there are simply two tables: Items +----+--------+ | id | title | +----+--------+ | 1 | socks | | 2 | banana | | 3 |…
James Lai
  • 2,051
  • 17
  • 14
2
votes
4 answers

how to get biggest result from a sql result in postgresql

I am using postgresql 8.3 and I have a simple sql query: SELECT a.id,a.bpm_process_instance_id,a.actor_id FROM bpm_task_instance a WHERE a.bpm_process_instance_id IN ( SELECT bpm_process_instance_id FROM incident_info …
diligent
  • 2,282
  • 8
  • 49
  • 64
1
vote
2 answers

ORDER BY DESC with LIMIT doesn't return the newest rows, unless I ORDER BY twice

I am writing a chat app and want to get a list of recent contacts to show in the sidebar. My table has the standard chat fields (sender, receiver, msg, date). I'm currently getting a list of recent contacts by getting a list of messages with…
Kernel James
  • 3,752
  • 25
  • 32
1
vote
2 answers

Using DISTINCT ON for a column of type timestamp to extract the most recent value for each day

I have a table named assets: create table assets ( id bigint primary key, name varchar(255) not null, value double precision not…
MehdiB
  • 870
  • 12
  • 34
1
vote
2 answers

PostgreSQL select distinct JSONB poor performance

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

What is the BigQuery equivalent of PostgreSQL's `DISTINCT ON`?

I am migrating some queries from PostgreSQL dialect over to BigQuery. One nice pattern in PostgreSQL is DISTINCT ON (key), which returns the first row for every key based on the sequence as defined in the ORDER BY section. Is there a similar pattern…
François Leblanc
  • 1,412
  • 1
  • 17
  • 23
1
vote
1 answer

How to use Distinct On with COALESCE

I'm trying to build a query which sums up a column for all rows with unique IDs (interested only in the latest entry for a given ID). The thing is the IDs are coming from one of 2 columns so I'm using COALESCE. This is what I roughly want: SELECT…
Martin
  • 1,159
  • 10
  • 34
1
vote
1 answer

How to remove duplicates in query for google big query by a subset of returned rows, and keep first?

In pandas, I can drop duplicates by a subset of columns and keep first by df = df.drop_duplicates(subset=['column1', 'column6'], keep='first') I am trying to figure out how to do the same in a query for Google big query. I saw that GROUP BY is what…
SantoshGupta7
  • 5,607
  • 14
  • 58
  • 116
1
vote
2 answers

How to pull max timestamp for distinct sets and subsets

EDIT: DBMS = Haddoop, Using Teradata SQL Asstistant This is the original table. There are 20 location values (c1). Each Location has a set of aisles (c2). I want to get all the set of records from this table for Distinct locations and their set of…
Number1Rock
  • 53
  • 4
1
vote
1 answer

Typeorm order by after distinct on with postgresql

I have a table below: id product_id price  1 1 100 2 1 150 3 2 120 4 2 190 5 3 100 6 3 80 I want to select cheapest price for product and sort them by price Expected…
I.Bozcan
  • 45
  • 1
  • 7
1
vote
1 answer

how to filter selected query with another condition in postgresql

i have table as below id | product_id | product_type_id | closing_stock | created_dttm ------------------------------------------------------------------------- 1 2 1 0 21-Nov-21 2 …
pappu_kutty
  • 2,378
  • 8
  • 49
  • 93
1
vote
1 answer

Count unique rows GROUP(ed) BY different columns than used in DISTINCT ON

I'm sure this has been asked over and over, but I can't quite find a simple example that I can fully grok. I'm trying to deduplicate (do a DISTINCT ON) by one column and COUNT records GROUPed By columns that differ from the column used to…
Savir
  • 17,568
  • 15
  • 82
  • 136
1
vote
1 answer

Is SELECT DISTINCT ON (col) * valid?

SELECT DISTINCT ON (some_col) * FROM my_table I'm wondering if this is valid and will work as expected. Meaning, will this return all columns from my_table, based on distinct some_col? I've read the Postgres docs and don't see any reason why this…
KayakinKoder
  • 3,243
  • 3
  • 23
  • 37
1
vote
1 answer

PostgreSQL distinct on, group by, having in one?

I'm am having some trouble with a PostgreSQL query What I want is a list of "Campaigns", ordered by distance, and within a minimum distance from a location [in_lat, in_lng, in_radius] Now, I am retrieving the distance (with distance_in_km(...) )…
Koen
  • 55
  • 1
  • 10
1
vote
1 answer

Select the maximum rows of sorted subgroups

Using PostgreSQL 11, I have a table containing a DAY and MONTH_TO_DAY entry for each day of every month. I would like to select the most recent MONTH_TO_DAY entry for each account. My table…
blueberryscones
  • 13
  • 2
1 2
3
4 5 6 7