0

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
jokonoo
  • 83
  • 1
  • 7
  • 1
    A [mcve] is a great start when asking for SQL assistance. Also include your current query attempt. – jarlh Apr 06 '23 at 11:09

2 Answers2

2
SELECT
   DISTINCT ON (employee_id) employee_id,
   id
FROM
   table_name
ORDER BY
   employee_id, id DESC;
Taavi Kivimaa
  • 242
  • 1
  • 7
  • Tried this once, ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions – jokonoo Apr 06 '23 at 11:27
  • I edited, this should work. Order of employee_id doesn't change anything as they are duplicates, so it will put with highest ID on top and select distinct from that. – Taavi Kivimaa Apr 06 '23 at 11:32
1

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;
SelVazi
  • 10,028
  • 2
  • 13
  • 29