CODEWITHSUNDEEP
sqloracle-database
sbrbot
sbrbot

Reputation: 6447

First and second elemnt from group

The following table contains activities and its tasks:

TASK_ID | TASK_NAME | ACT_ID
========+===========+=======
1000    | A         | 300
1001    | B         | 300
1002    | C         | 300
1003    | A         | 400
1004    | D         | 400
1005    | B         | 500
1006    | C         | 500
1007    | D         | 500
1008    | A         | 600

I need an SQL query which will in pivotal form represent activities and its first and second task only. The result should look like this:

ACT_ID | Task1st | Task2nd
=======+=========+========
300    | A       | B
400    | A       | D
500    | B       | C
600    | A       | NULL

Upvotes: 0

Views: 44

Answers (2)

Javaluca
Javaluca

Reputation: 857

SELECT a.ACT_ID, a.TASK_NAME, b.TASK_NAME
FROM activities a
LEFT JOIN activities b
ON ( a.ACT_ID = b.ACT_ID
     AND b.TASK_ID > a.TASK_ID
     AND NOT EXISTS ( SELECT 'y'
                      FROM activities b2
                      WHERE b2.ACT_ID = b.ACT_ID
                      AND b2.TASK_ID > a.TASK_ID 
                      AND b2.TASK_ID < b.TASK_ID 
                     )
    )
WHERE NOT EXISTS ( SELECT 'x'
                   FROM activities a2
                   WHERE a2.ACT_ID = a.ACT_ID
                   AND a2.TASK_ID < a.TASK_ID 
                  )

If you need a ansi-sql query can try this one. P.S. I have not tried with data let me know if it works.

Upvotes: 0

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Use below query with pivot 

select * from
    (
    select act_id,task_name,rn from 
    (
    select distinct act_id,task_name,row_number() over (partition by act_id order by act_id) rn from table1 
    ) where rn<=2
    )
    pivot
    (
    min(task_name) as task for rn in ( 1  ,2  )
    )
    order by act_id

Upvotes: 1

Related Questions