CODEWITHSUNDEEP
sqlpostgresqlduplicatesdistinct
me--
me--

Reputation: 2148

Removing duplicates *after* ordering in a SQL query

I have the following SQL query:

select 
    id,
    name
from
    project
    inner join job on project.id = job.project_id
where
    job.user_id = 'me'
order by
    project.modified desc
limit 10

The idea is to get information about the 10 most recently used projects for a given user.

The problem is that this can return duplicates in the case where multiple jobs have the same project. Instead of having duplicates, I want to order all rows by modified desc, remove duplicates based on id and name, then limit to 10.

I've not been able to figure out how to achieve this. Can anyone point me in the right direction?

Upvotes: 0

Views: 133

Answers (4)

me--
me--

Reputation: 2148

I ended up with the following, which seems to work fine:

select
    p.id,
    p.name
from
    project p
    inner join
    (
        select
            j.id,
            max(j.modified) as max_modified
        from
            job j
        where
            t.user_id = 'me'
        group by
            j.id
        order by
            max_modified desc
        limit 10
    ) ids on p.id = ids.id
order by
    max_modified desc

Upvotes: 0

user330315
user330315

Reputation:

You are getting duplicates because of the join. As you only want columns from the project table (I assume id and name are from that table), not creating the duplicates in the first place would be better than removing them after the join:

select p.id,
       p.name
from project p
where exists (select *
              from job job
              where job.project_id = p.id
                and job.user_id = 'me')
order by p.modified desc
limit 10

Upvotes: 1

DanielGS
DanielGS

Reputation: 109

Did you try SELECT DISTINCT?

select distinct
    id,
    name
from
    project
    inner join job on project.id = job.project_id
where
    job.user_id = 'me'
order by
    project.modified desc
limit 10

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

try this using row_number - it will work on postgresql

select * from 
(select 
    id,
    name,row_number() over(partition by id,name order by modified desc) as rn
from
    project inner join job on project.id = job.project_id
where
    job.user_id = 'me')a where rn=1 order by modified desc limit 10

Upvotes: 0

Related Questions