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, 2022 in Database by Kithuzzz
• 38,000 points
701 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, 2022 by narikkadan
• 63,600 points

Related Questions In Database

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,370 points
15,319 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
764 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,370 points
22,103 views
0 votes
1 answer

What do you understand by Database Triggers?

A set of commands that automatically get ...READ MORE

answered Nov 14, 2018 in Database by Sahiti
• 6,370 points
778 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
1,303 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
857 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
1,026 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,350 points
829 views
0 votes
1 answer

What does the "@" symbol do in SQL?

The @CustID denotes a parameter for which ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,600 points
1,317 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, 2022 in Database by narikkadan
• 63,600 points
721 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP