CODEWITHSUNDEEP
sqlpostgresql
Aravind S
Aravind S

Reputation: 495

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

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.

Upvotes: 1

Views: 2941

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In Postgres, you can use distinct on:

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

Upvotes: 3

Philipp T.
Philipp T.

Reputation: 803

How about wrapping the whole query into

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

?

Upvotes: 0

Related Questions