Unique rows from PostgreSQL database and ordering

Question

how can I get unique rows from PostgreSQL (8.4.20), at this moment my query looks like:

SELECT 
    DISTINCT ON(student.id) student.id, 
    student.*,
    programme_stage.*
FROM  
    person AS student 
INNER JOIN 
    programme ON (student.id = programme.person_id) 
RIGHT JOIN 
    programme_stage ON (programme.id = programme_stage.programme_id) 
ORDER BY 
    student.id, 
    student.last_name ASC, 
    student.first_name ASC 
LIMIT 10 OFFSET 0

The above query works correctly, but I want to sort by the last and first name in first order.

Sample data:

| id | first_name | last_name | programme_id | programme_stage _id |
|----|------------|-----------|--------------|---------------------|
| 1  | Michał     | Nowak     | 1            | 1                   |
| 2  | Jan        | Kowalski  | 2            | 2                   |
| 3  | Tomasz     | Thomas    | 2            | 1                   |

Expected output:

| id | first_name | last_name | programme_id | programme_stage _id |
|----|------------|-----------|--------------|---------------------|
| 2  | Jan        | Kowalski  | 2            | 2                   |
| 1  | Michał     | Nowak     | 1            | 1                   |
| 3  | Tomasz     | Thomas    | 2            | 1                   |

If I try to remove from order statment student.id column, I getting error:

..SELECT DISTINCT ON expressions must match the expressions .. ORDER BY LINE 2:  DISTINCT ON(student_person.id) student_person.id, 
                 ^

Are you really still using 8.4? That has been out of support for many years now — user330315
– user330315, Commented Feb 20, 2018 at 12:00
Please edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. (edit your question - do not post code or additional information in comments) — user330315
– user330315, Commented Feb 20, 2018 at 12:01

Gordon Linoff · Accepted Answer · 2018-02-20 12:03:31Z

1

Use a subquery:

SELECT s.*
FROM (SELECT DISTINCT ON (s.id) s.*, ps.*
      FROM programme_stage ps LEFT JOIN
           programme p
           ON programme.id = ps.programme_id LEFT JOIN
           person s
           ON s.id = p.person_id)
      ORDER BY s.id
      LIMIT 10 OFFSET 0
     ) s
ORDER BY s.last_name ASC, s.first_name ASC;

Notes:

I prefer shorter table aliases so the query is easier to write and to read.
I prefer LEFT JOIN to RIGHT JOIN. However, because you are using the person table for aggregation, you probably want inner joins.
There is no need to select the id twice.
You may need to select explicit columns instead of * if columns have the same name.

answered Feb 20, 2018 at 12:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

JohnTemp Over a year ago

I have about 50 00 rows and your example, sort only first 10 (from subquery) :/

Gordon Linoff Over a year ago

@JohnTemp . . . The limit 10 is in your code. If you want all rows, then remove it.

JohnTemp Over a year ago

I want to sort alphabetically all rows (50 000) and after this get first 10 unique records. In your example, ten first rows are taken first and only them are being sorted.

Gordon Linoff Over a year ago

@JohnTemp . . . The inner join sorts by s.id, which is what your query does. Change the order by keys to be what you want.

Collectives™ on Stack Overflow

Unique rows from PostgreSQL database and ordering

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related