problem in writing a Query

[san1014]

Hi,
I have a table
SQL> select * fom temp;
FID TID RN
---------- ---------- ----------
1 2 1
1 3 2
1 4 3
2 1 1
2 5 2
3 1 1
3 6 2
3 7 3
4 1 1


Now i want the o/p like this

FID TID's
------ ---------------------------
1 2,3,4
2 1,5
3 1,6,7
4 1

Any Help?

thank you

[amitpatel66]

Hi,
I have a table
SQL> select * fom temp;
FID TID RN
---------- ---------- ----------
1 2 1
1 3 2
1 4 3
2 1 1
2 5 2
3 1 1
3 6 2
3 7 3
4 1 1


Now i want the o/p like this

FID TID's
------ ---------------------------
1 2,3,4
2 1,5
3 1,6,7
4 1

Any Help?

thank you

 
SELECT * FROM
(SELECT fid,sn, row_number() over(partition by fid order by fid,sn desc) rn from
(SELECT fid, SUBSTR(SYS_CONNECT_BY_PATH(rid,','),2) sn  FROM
(select fid,rid,row_number() OVER(PARTITION BY fid ORDER BY FID) rn  FROM
(select 1 as fid, 2 as rid from dual
UNION
select 1, 3 from dual
union
select 1, 4 from dual
UNION
select 2, 1 from dual
UNION
SELECT 2, 2 from dual
UNION
SELECT 3, 4 from dual
UNION
SELECT 3, 6 FROM Dual)) 
CONNECT BY rn = PRIOR rn - 1
AND PRIOR fid = fid
ORDER BY sn desc, fid desc))
WHERE rn = 1
 
--output
 
FID    SN    RN
1    4,3,2    1
2    2,1    1
3    6,4    1
 
 

The above query looks slightly complex becuase i dont have a table structure and had to make it work using dual statements for the data.