postgresql : Get only a single record from result containing multiple rows

Question

I've written a query that lists all the tasks for which has efforts applied to it. The query which I've written, in a case where if the same task is having different efforts applied, even that is getting listed.

For example, if task1 has 3 different efforts applied,that is, 2.5, 6, 3 hours, then these are getting listed. I'm trying to list only the first entry that is, only the entry which has 2.5 hours for task1.

Here's the query which I've written that lists all the tasks with joins to different tables :

 SELECT t.taskid AS id,
    t.projectid AS project,
    te.effort AS effort,
    (((t.taskname::text || ' | '::text) || m.milestonename::text) || ' | '::text) || p.projectname::text AS name,
    t.status,
    1 AS version
   FROM task t
     LEFT JOIN timeentry te ON t.taskid = te.taskid
     LEFT JOIN project p ON t.projectid = p.projectid
     LEFT JOIN milestonetask tm ON t.taskid = tm.taskid
     FULL JOIN milestone m ON tm.milestoneid = m.milestoneid
  WHERE m.milestoneid IS NOT NULL
UNION
 SELECT distinct(t.taskid) AS id,
    t.projectid AS project,
    te.effort AS effort,
    (t.taskname::text || ' | (--no milestone--) | '::text) || p.projectname::text AS name,
    t.status,
    1 AS version
  FROM task t 
     LEFT JOIN timeentry te ON t.taskid = te.taskid
     LEFT JOIN project p ON t.projectid = p.projectid
     LEFT JOIN milestonetask tm ON t.taskid = tm.taskid
     FULL JOIN milestone m ON tm.milestoneid = m.milestoneid
  WHERE NOT (tm.taskid IN ( SELECT DISTINCT milestonetask.taskid
           FROM milestonetask
          WHERE milestonetask.milestoneid IS NOT NULL));

Here's the result which I'm getting now :

id | project | effort |        name       | status | version 
----+---------+--------+------------------+--------+---------
 89 |       1 |      5 | Coding for clock | Active |       1
 89 |       1 |      2 | Coding for clock | Active |       1
 89 |       1 |      4 | Coding for clock | Active |       1

Instead of these multiple rows for the same task, I'm trying to have only one row for the taskid 89, provided it has efforts applied.

Please let me know if I'm clear enough, would greatly appreciate your help and thanks in advance.

Gordon Linoff · Accepted Answer · 2018-06-18 11:22:29Z

3

In Postgres, you can use distinct on:

with t as (
      <your query here>
     )
select distinct on (id) t.*
from t
order by id;

answered Jun 18, 2018 at 11:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aravind S Over a year ago

Apologies for the delayed response. And it worked, thanks a lot :)

Philipp T. · Accepted Answer · 2018-06-18 07:05:34Z

0

How about wrapping the whole query into

SELECT DISTINCT(id), * FROM (
<your query>
) subquery;

?

answered Jun 18, 2018 at 7:05

Philipp T.

8035 silver badges13 bronze badges

1 Comment

user330315 Over a year ago

distinct is NOT a function. distinct (a), b is exactly the same thing as distinct a,b or distinct a, (b)

Collectives™ on Stack Overflow

postgresql : Get only a single record from result containing multiple rows

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related