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
0
votes
1 answer

DISTINCT ON to find min and max times

I have tried using DISTINCT ON with posrgresql to achieve the following: Lets say I have a table that looks like this: id time price 1 12:00 10 1 13:00 20 1 14:00 30 And my goal is to create a table with only 1 row per id, that shows a…
Tomer Shalhon
  • 185
  • 1
  • 11
0
votes
1 answer

trying to get only one row from each group

I have a problem with DISTINCT ON. I have five different groups of people which include names and surnames. My goal is to get only one name per group (the first one). When try to use DISTINCT ON, I got an error. SELECT DISTINCT ON (group_A) surname…
betd1
  • 81
  • 7
0
votes
2 answers

SELECT DISTINCT ON in Postgresql returns multiple rows

When I run SELECT DISTINCT ON (ts) * FROM tbl WHERE clause=100 AND ts <= '2018-04-02 15:11:18.819000' AND ts > '2018-04-02 15:06:18.819000' ORDER BY ts, version ASC; I expect a single row corresponding to the largest ts that meets the…
bphi
  • 3,115
  • 3
  • 23
  • 36
0
votes
1 answer

Why doesn't my DISTINCT ON expression work?

Query: SELECT DISTINCT ON (geom_line),gid FROM edge_table; I have a edge table which contains duplicates and I want to remove duplicate edges keeping one of them, but the syntax itself is wrong?
shivam thakur
  • 109
  • 10
0
votes
0 answers

DISTINCT ON with aggregate function

I want to get the project with the highest number of views for each user in Postgres. This is the code I have so far: SELECT DISTINCT ON (user_id) project, user_id, COUNT(view_id) views FROM table_name GROUP BY user_id ORDER BY …
Marco Greselin
  • 213
  • 1
  • 3
  • 5
0
votes
2 answers

Postgres select distinct of cartesian product

How to select from two columns such that each value is seen only once? For example. From this table: Column A Column…
Eric
  • 1,093
  • 1
  • 9
  • 21
0
votes
3 answers

PLPGSQL distinct on, order by, returns nothing

I made this plpgsql function and it doest return me anything! while if i take out the query part and execute this in a seperate sql window it returns the correct rows. I also think the query isnt really optimal so any help is appreciated (very new…
Koen
  • 55
  • 1
  • 10
0
votes
1 answer

Access - Select Distinct based on Field Value

My Code I have an Access query: SELECT DISTINCT [Request-Priority, ALL_Q].RequestID, "Awaiting Approval" AS RequestStatus FROM [Request-Priority, ALL_Q] LEFT JOIN LIST_RequestBurndown_Q ON [Request-Priority, ALL_Q].RequestID =…
TMY
  • 471
  • 1
  • 7
  • 20
0
votes
2 answers

Sort by id desc on multiple columns distinct postrges

SELECT impressions.* FROM impressions WHERE impressions.user_id = 2 AND impressions.action_name = 'show' AND (impressions.message IS NOT NULL) GROUP BY impressionable_id, impressionable_type I'd like to select from the table all last…
user1320651
  • 808
  • 2
  • 15
  • 42
0
votes
1 answer

Distinct Only 1 column

I am trying to write a query to return the sum of call time for each specific category. There are only 3 categories and i only want 3 rows to be returned with the sum of time. I thought using the distinct would work, but it also applies to the…
MidnightPolaris
  • 1
  • 1
0
votes
1 answer

postgresql - distinct only on one column

I have a table called eatable. It has columns such as type, name. type column has fruit, veggie, veggie, fruit, veggie values . name column has apple, brinjal, carrot, banana, cabbage.values I want the output as, type column must display only 2…
zeewagon
  • 1,835
  • 4
  • 18
  • 22
0
votes
2 answers

PostgreSQL changes result when i play with offset, limit

Hi guys im using the below query If i change limit to 10 or 20 the results are different I'm learning postgresql i couldn't figure it out what is the problem Is there postgresql specific warning ? WITH RECURSIVE children AS ( SELECT id,…
Can Tecim
  • 577
  • 8
  • 18
0
votes
1 answer

Use DISTINCT ON with empty n:n relations

I'm a new user of PostgreSQL, trying to use DISTINCT ON but I can't reach my goal. Here's a brief sketch of my database : files with versioning fields with model (for form generation purpose) n:n relations between files' versions and fields I…
zessx
  • 68,042
  • 28
  • 135
  • 158
0
votes
0 answers

Postgresql: faster alternative to SELECT DISTINCT ON

I have a problem with copying filtered data from one table to another (with similar structure). Basically, I have one big table with about 11 billion rows, and I'd like to copy to another table records that are unique based on one of the columns…
barti90
  • 15
  • 1
  • 5
-1
votes
1 answer

How to get the best-rated row for each group of rows in *standard* SQL *efficiently*?

The Question I have two SQL tables albums and photos. Albums can be hierarchically nested as a tree and use the nested set paradigm. Photos are children of albums. I need an SQL query which returns the ID of the cover photo for each album ID. The…
user2690527
  • 1,729
  • 1
  • 22
  • 38
1 2 3 4 5
6
7