1

My table

id           updateddate        updatedbyuser   department
-------------------------------------------------------
40715449759  12/7/2018 12:24            1260    IB
40715449759  12/8/2018 10:40            1203    ESCALATION
40715449759  12/8/2018 10:40            task
40715449759  12/9/2018 9:58           client
40715449759  12/9/2018 12:09       300050282    ESCALATION
42058447691  12/23/2018 16:44           1712    TASK
42058447691  12/26/2018 19:33           4700    ESCALATION
42058447691  12/27/2018 17:26           7357    ESCALATION

Output

id            first_action_date
--------------------------------
40715449759   12/8/2018 10:40
42058447691   12/26/2018 19:33

I'm trying to extract id and first_action_time from mytable where the first instance of updateddate is first_action_date where the department is ESCALATION for a unique id

Pulicherla Sandeep Reddy
  • 21
  • 5

3 Answers3

1

Just use an aggregation with min as 

select id, 
       min(first_action_date) 
       as first_action_date
  from mytable
 where department = 'ESCALATION'
 group by id
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
1

Postgres has a feature called distinct on, which can be used in this case:

select distinct on (id) id, first_action_date
from t
where department = 'ESCALATION'
order by id, first_action_date asc;

Although your particular query can be handled directly by group by, this is handy if you want other columns, such as the user. To get the entire row:

select distinct on (id) t.*
from t
where department = 'ESCALATION'
order by id, first_action_date asc;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
select id, updateddate as first_action_date from table where department= 'ESCALATION' order by id
Abhishek Rana
  • 107
  • 2
  • 14
  • 1
    While this code may answer the question, it is better to explain how to solve the problem and provide the code as an example or reference. Code-only answers can be confusing and lack context. – Dima Kozhevin Dec 29 '18 at 13:04