英文:
Postgresql distinct on 1 column with highest possible second column
问题
我有一个在PostgreSQL中的表，有2列：
- id
- employee_id
在这个表中，有一些带有重复employee_id但总是唯一id的记录。我需要获取具有最高可能id的唯一employee_id。我想要实现的是基于employee_id的不重复查询，以获取可能id最高的记录。所以，如果有2条具有相同employee_id但其中一条id更高，那么我想获取具有更高id的那一条。
尝试了这个查询：
SELECT DISTINCT ON (employee_id) id, employee_id
FROM table_name
ORDER BY id DESC;
但首先，order by需要具有与distinct on相同的值，其次，在我的情况下不起作用，因为它会在选择不同之后对记录进行排序。
第二次尝试也没有得到正确的记录：
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 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.
答案1
得分: 2
SELECT
DISTINCT ON (employee_id) employee_id,
id
FROM
table_name
ORDER BY
employee_id, id DESC;
英文:
SELECT
DISTINCT ON (employee_id) employee_id,
id
FROM
table_name
ORDER BY
employee_id, id DESC;
答案2
得分: 1
你可以使用 group by 和 max() 来实现：
select employee_id, max(id)
from table_name
group by employee_id
order by employee_id;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论