What does DISTINCT ON expression do

0 votes

I am aware of how DISTINCT operates, but I am baffled by DISTINCT ON (expression).

Consider the first illustration in this screenshot:

enter image description here

What effect does the portion (a% 2) have on everything? Is it saying to just return if the returned value is distinct yet a% 2 evaluates to true before continuing to do so for all other tuples?

Sep 14 in Database by Kithuzzz
• 11,640 points
15 views

1 answer to this question.

0 votes

The snippet from the Official documentation for PostgreSQL is as follows.

DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. [...] Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. [...] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).

The first thing to note is that, for reasons that, hopefully, will soon become evident, whatever you put in the ON (), must be first in the ORDER BY.

SELECT DISTINCT ON (a) a, b, c FROM a_table ORDER BY a, b

The results are then filtered such that just the first row is really returned for each of the different entities.

For example...

CREATE TABLE example (
    id               INT,
    person_id        INT,
    address_id       INT,
    effective_date   DATE
);

INSERT INTO
    example (id, person_id, address_id, effective_date)
VALUES
    (1, 2, 1, '2000-01-01'),  -- Moved to first house
    (5, 2, 2, '2004-08-19'),  -- Went to uni
    (9, 2, 1, '2007-06-12'),  -- Moved back home

    (2, 4, 3, '2007-05-18'),  -- Moved to first house
    (3, 4, 4, '2016-02-09')   -- Moved to new house
;

SELECT DISTINCT ON (person_id)
    *
FROM
    example
ORDER BY
    person_id,
    effective_date DESC
;

I hope this helps you.

answered Sep 16 by narikkadan
• 19,680 points

Related Questions In Database

0 votes
0 answers

What does the "@" symbol do in SQL?

I was browsing through the questions and ...READ MORE

Sep 8 in Database by Kithuzzz
• 11,640 points
16 views
0 votes
1 answer

What does recursive stored procedure?

Recursive stored procedure refers to a stored ...READ MORE

answered Sep 27, 2018 in Database by Sahiti
• 6,380 points
10,857 views
0 votes
1 answer

How do I enable Oracle automatically on systemboot?

To enable the database service to start ...READ MORE

answered Oct 5, 2018 in Database by Frankie
• 9,830 points
217 views
0 votes
1 answer

What do ‘Record’, ‘Field’ and ‘Table’ mean in terms of a database?

You can refer to the following definitions ...READ MORE

answered Nov 12, 2018 in Database by Sahiti
• 6,380 points
10,341 views
0 votes
2 answers

Install postgreSQL on Ubuntu

Follow the below commands to install PostgreSQL (PSQL) ...READ MORE

answered Nov 12, 2020 in Database by Prachi
• 140 points
428 views
0 votes
1 answer

Access progrs prompt without switching accounts

Use the following to get the postgres prompt ...READ MORE

answered Mar 22, 2019 in Database by Mahi
342 views
0 votes
1 answer

Create new role - postgresql on ubuntu

If you are logged in as the postgres account, ...READ MORE

answered Mar 22, 2019 in Database by Danny
284 views
0 votes
1 answer

How to perform Near-real time analytics using Hadoop?

To perform Near-real time analytics, you have ...READ MORE

answered Apr 24, 2018 in Big Data Hadoop by kurt_cobain
• 9,390 points
342 views
0 votes
1 answer

What does SELECT COUNT(1) from table_name on any database table mean

An expression to be tested for each ...READ MORE

answered Sep 19 in Database by narikkadan
• 19,680 points
14 views
0 votes
1 answer

How do I create a table based on another table

Although CREATE TABLE AS... SELECT does exist ...READ MORE

answered Sep 12 in Database by narikkadan
• 19,680 points
28 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP