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

[code=oracle]

SELECT * FROM
(SELECT fid,sn, row_number() over(partition by fid order by fid,sn desc) rn from
(SELECT fid, SUBSTR(SYS_CONN ECT_BY_PATH(rid ,','),2) sn FROM
(select fid,rid,row_num ber() 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

[/code]

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.