Question 89079 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql distinct on 1 column with highest possible second column

I have table in PostgreSQL with 2 columns:

  • id
  • employee_id

In this table there are some records with duplicated employee_id but always unique id. I need to get unique employee_id with highest id possible. What i want to achieve is distinct query basing on employee_id, with highest id possible. So if there are 2 records with same employee_id but one with higher id than the other, then i want to get the one with higher id.

Tried this query:

SELECT DISTINCT ON (employee_id) id, employee_id
FROM table_name
ORDER BY id DESC;

But first of all order by needs to have the same value that distinct on, secondly i would not work in my case cause it would order records after select distinct.

Second attempt also did not result in correct records:

SELECT *
FROM
(
    SELECT DISTINCT ON (employee_id) *
    FROM
    (
        SELECT *
        FROM table_name
        ORDER BY id DESC
    ) AS sub
) AS sub2
ORDER BY id DESC;

I would be very grateful for help and tips.

Tags:

2

Answers


  1. 0 
    SELECT
   DISTINCT ON (employee_id) employee_id,
   id
FROM
   table_name
ORDER BY
   employee_id, id DESC;
    Login or Signup to reply.
  2. 0

    You can do it using groub by and max() :

    select employee_id, max(id)
from table_name
group by employee_id
order by employee_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search