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
329
votes
7 answers

PostgreSQL DISTINCT ON with different ORDER BY

I want to run this query: SELECT DISTINCT ON (address_id) purchases.address_id, purchases.* FROM purchases WHERE purchases.product_id = 1 ORDER BY purchases.purchased_at DESC But I get this error: PG::Error: ERROR: SELECT DISTINCT ON expressions…
sl_bug
  • 5,066
  • 5
  • 21
  • 22
44
votes
4 answers

Selecting rows ordered by some column and distinct on another

Related to - PostgreSQL DISTINCT ON with different ORDER BY I have table purchases (product_id, purchased_at, address_id) Sample data: | id | product_id | purchased_at | address_id | | 1 | 2 | 20 Mar 2012 21:01 | 1 | | 2 | …
sl_bug
  • 5,066
  • 5
  • 21
  • 22
24
votes
1 answer

PostgreSQL: Select only the first record per id based on sort order

For the following query I need to select only the first record with the lowest shape_type value (ranges from 1 to 10). If you have any knowledge on how to easily do this is postgresql, please help. Thanks for your time. select g.geo_id,…
Sarah Bergquist
  • 375
  • 2
  • 6
  • 10
19
votes
3 answers

Applying a different order to postgres "DISTINCT ON" using rails

I have a rails app: user has_many :projects user has_many :tasks, :through => :projects project has_many :tasks Each task has a milestone date. To show a table of project details with next milestone date I am using: @projects =…
user1116573
  • 2,817
  • 4
  • 17
  • 27
18
votes
5 answers

PostgreSQL does not allow me to group a column with order

In PostgreSQL i want to fetch every users at once and order them by date. This is my query: SELECT id, useridx, isread, message, date FROM messages WHERE isread = 1 GROUP BY useridx ORDER BY date DESC This is a sample…
flower58
  • 687
  • 2
  • 8
  • 15
15
votes
4 answers

distinct() function (not select qualifier) in postgres

I just came across a SQL query, specifically against a Postgres database, that uses a function named "distinct". Namely: select distinct(pattern) as pattern, style, ... etc ... from styleview where ... etc ... Note this is NOT the ordinary DISTINCT…
Jay
  • 26,876
  • 10
  • 61
  • 112
11
votes
2 answers

Multiple DISTINCT ON clauses in PostgreSQL

Is it possible to select rows that are DISTINCT ON some separate, independent sets of columns? Suppose I want all the rows which match the following conditions: distinct on (name, birth) distinct on (name, height) So that, out of the following…
Jivan
  • 21,522
  • 15
  • 80
  • 131
7
votes
2 answers

How to use DISTINCT ON (of PostgreSQL) in Firebird?

I have a TempTable with datas: ------------------------------------ | KEY_1 | KEY 2 | NAME | VALUE | ------------------------------------ | 1 | 0001 | NAME 2 | VALUE 1 | | 1 | 0002 | NAME 1 | VALUE 3 | | 1 | 0003 | NAME 3 | VALUE…
Fox Vĩnh Tâm
  • 150
  • 1
  • 2
  • 15
6
votes
2 answers

Select Distinct on one column, without ordering by that column

I'm trying to select only the IDs of a table that I'm querying on, and still be able to specify ordering on other columns. First I tried simply doing: SELECT DISTINCT countries.id FROM countries ... ORDER BY province_infos.population DESC,…
robbieperry22
  • 1,753
  • 1
  • 18
  • 49
6
votes
2 answers

Redshift PostgreSQL Distinct ON Operator

I have a data set that I want to parse for to see multi-touch attribution. The data set is made up by leads who responded to a marketing campaign and their marketing source. Each lead can respond to multiple campaigns and I want to get their first…
Berra2k
  • 318
  • 2
  • 5
  • 16
5
votes
1 answer

How to use "DISTINCT ON" in conjunction with Arel / ActiveRecord?

I have a Message ActiveRecord Model and I want to select all messages that have a distinct conversation_partner_id. I'm currently specifying the DISTINCT ON clause as a string but I was wondering if it could be provided as an Arel expression instead…
t6d
  • 2,595
  • 3
  • 26
  • 42
5
votes
1 answer

How to order distinct tuples in a PostgreSQL query

I'm trying to submit a query in Postgres that only returns distinct tuples. In my sample query, I do not want duplicate entries where an entry exists multiple times for a cluster_id/feed_id combination. If I do a simple: select distinct on…
WildBill
  • 9,143
  • 15
  • 63
  • 87
4
votes
1 answer

Most recent inner query in activerecord

I have a table users: id first_name -------------- 1 Bill 2 Denise who read multiple books: id book user_id read_at --------------------------------------------------- 1 Garry Potter 1 2020-1-1 2 …
Mike Neumegen
  • 2,436
  • 1
  • 24
  • 39
3
votes
2 answers

How to get first row of data for each month (postgres)

I am a beginner in postgres and would like to get the first row for each month (group by) but I'm not really sure how. My table order is as per below: order_id | cust_id | order_date ------------------------------------------------ order1 | cust1 …
tiredqa_18
  • 162
  • 2
  • 9
3
votes
3 answers

How do I take a DISTINCT ON subquery that is ordered by a separate column, and make it fast?

(AKA - With a query and data very similar to question "Selecting rows ordered by some column and distinct on another", how can I get it to run fast). Postgres 11. I have table prediction with (article_id, prediction_date, predicted_as,…
mjames
  • 220
  • 2
  • 13
1
2 3 4 5 6 7