PostgreSQL: Combine Count and DISTINCT ON

Question

Given this table

| id | name  | created_at                 |
| 1  | test  | 2015-02-24 11:13:28.605968 |
| 2  | other | 2015-02-24 13:04:56.968004 |
| 3  | test  | 2015-02-24 11:14:24.670765 |
| 4  | test  | 2015-02-24 11:15:05.293904 |

And this query which returns only the rows id 2 and id 4.

SELECT DISTINCT ON (documents.name) documents.*
FROM "documents"  
ORDER BY documents.name, documents.created_at DESC

How can i return the number of rows affected? Something like

SELECT COUNT(DISTINCT ON (documents.name) documents.*) FROM "documents"

klin · Accepted Answer · 2015-06-22 13:33:13Z

5

You can use an outer query:

SELECT COUNT(1)
FROM (
    SELECT DISTINCT ON (name) *
    FROM documents
    ORDER BY name, created_at DESC
    ) alias

answered Jun 22, 2015 at 13:33

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PainIsAMaster Over a year ago

As soon as you start adding more columns than "name" it doesn't work, FYI. Distinct ON will take each individual column for uniqueness, where distinct col1,col2,col3 checks the entire combination.

Collectives™ on Stack Overflow

PostgreSQL: Combine Count and DISTINCT ON

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related