Remove Duplicate Result on Query

could help me solve this duplication problem where it returns more than 1 result for the same record I want to bring only 1 result for each id, and only the last history of each record.

My Query:

SELECT DISTINCT ON(tickets.ticket_id,ticket_histories.created_at)
        ticket.id AS ticket_id,
        tickets.priority,
        tickets.title,
        tickets.company,
        tickets.ticket_statuse,
        tickets.created_at AS created_ticket,
        group_user.id AS group_id,
        group_user.name AS user_group,
        ch_history.description AS ch_description,
        ch_history.created_at AS ch_history
      FROM
        tickets
      INNER JOIN company ON (company.id = tickets.company_id)
      INNER JOIN (SELECT id,
        tickets_id,
        description,
        user_id,
        MAX(tickets.created_at) AS created_ticket
       FROM
        ch_history
       GROUP BY id,
        created_at,
        ticket_id,
        user_id,
        description
      ORDER BY created_at DESC LIMIT 1) AS ch_history ON (ch_history.ticket_id = ticket.id)
      INNER JOIN users ON (users.id = ch_history.user_id)
      INNER JOIN group_users ON (group_users.id = users.group_user_id)
      WHERE company = 15
      GROUP BY
        tickets.id,
        ch_history.created_at DESC;

Result of my query, but returns 3 or 5 identical ids with different histories I want to return only 1 id of each ticket, and only the last recorded history of each tick

 ticket_id | priority |                title                | company_id | ticket_statuse |       created_ticket       |                     company                          |  user_group     | group_id |     ch_description      |        ch_history       
            -----------+------------+--------------------------------------+------------+-----------------+----------------------------+------------------------------------------------------+-----------------+----------+------------------------+----------------------------
                 49713 |          2 | REMOVE DATA                       |      1 | t               | 2019-12-09 17:50:35.724485 | SAME COMPANY                                         | people          |        5 | TEST 1                  | 2019-12-10 09:31:45.780667
                 49706 |          2 | INCLUDE DATA                      |      1 | f               | 2019-12-09 09:16:35.320708 | SAME COMPANY                                         | people          |        5 | TEST 2                  | 2019-12-10 09:38:52.769515
                 49706 |          2 | ANY TITLE                         |      1 | f               | 2019-12-09 09:16:35.320708 | SAME COMPANY                                         | people          |        5 | TEST 3                  | 2019-12-10 09:39:22.779473
                 49706 |          2 | NOTING ELSE MAT                   |      1 | f               | 2019-12-09 09:16:35.320708 | SAME COMPANY                                         | people          |        5 | TESTE 4                 | 2019-12-10 09:42:59.50332
                 49706 |          2 | WHITESTRIPES                      |      1 | f               | 2019-12-09 09:16:35.320708 | SAME COMPANY                                         | people          |        5 | TEST 5                  | 2019-12-10 09:44:30.675434

wanted to return as below

 ticket_id | priority |                title                | company_id | ticket_statuse |       created_ticket       |                     company                       |  user_group     | group_id |     ch_description      |        ch_history       
-----------+------------+--------------------------------------+------------+-----------------+----------------------------+------------------------------------------------------+-----------------+----------+------------------------+----------------------------
     49713 |          2 | REMOVE DATA                       |      1 | t               | 2019-12-09 17:50:10.724485 | SAME COMPANY                                         | people          |        5 | TEST 1                  | 2020-01-01 18:31:45.780667
     49707 |          2 | INCLUDE DATA                      |      1 | f               | 2019-12-11 19:22:21.320701 | SAME COMPANY                                         | people          |        5 | TEST 2                  | 2020-02-05 16:38:52.769515
     49708 |          2 | ANY TITLE                         |      1 | f               | 2019-12-15 07:15:57.320950 | SAME COMPANY                                         | people          |        5 | TEST 3                  | 2020-02-06 07:39:22.779473
     49709 |          2 | NOTING ELSE MAT                   |      1 | f               | 2019-12-16 08:30:28.320881 | SAME COMPANY                                         | people          |        5 | TESTE 4                 | 2020-01-07 11:42:59.50332
     49701 |          2 | WHITESTRIPES                      |      1 | f               | 2019-12-21 11:04:00.320450 | SAME COMPANY                                         | people          |        5 | TEST 5                  | 2020-01-04 10:44:30.675434

I wanted to return as shown below, see that the field ch_description, and ch_history bring only the most recent records and only the last of each ticket listed, without duplication I wanted to bring this way could help me.

Answer

Two things jump out at me:

1. You have listed “created at” as part of your “distinct on,” which is going to inherently give you multiple rows per ticket id (unless there happens to be only one)

2. The distinct on should make the subquery on the ticket history unnecessary… and even if you chose to do it this way, you again are going on the “created at” column, which will give you multiple results. The ideal subquery, should you choose this approach, would have been to group by ticket_id and only ticket_id.

Slightly related:

3. An alternative approach to the subquery would be an analytic function (windowing function), but I’ll save that for another day.

I think the query you want, which will give you one row per ticket_id, based on the history table’s created_at field would be something like this:

select distinct on (t.id)
  <your fields here>
from
  tickets t
  join company c on t.company_id = c.id
  join ch_history ch on ch.ticket_id = t.id
  join users u on ch.user_id = u.ud
  join group_users g on u.group_user_id = g.id
where
  company = 15
order by
  t.id, ch.created_at  -- this is what tells distinct on which record to choose