User function

[dowens]

Need a user function that will return top 3 status by seqno desc

data looks like

key seqno status
1 1 r
1 2 s
1 3 p
1 4 x

The function has to go in a query.
I need 'xps' returned.

Please can any one help me to create this user function?

[krishnaindia2007]

Use the following query

SELECT key,seqno,status
FROM
(
SELECT *
FROM tablename
WHERE status IN('X','P','S')
ORDER BY seqno
)
WHERE ROWNUM<=3

It will return top 3 records having status xps

[susarlasireesha]

To Return 'xps' then use
CREATE OR REPLACE function fun1 return varchar as
cursor c1 is SELECT status
FROM (
SELECT key,seqno,status,rank()
OVER (ORDER BY seqno DESC) EMPRANK
FROM test_1)
WHERE emprank <=3;
c1r c1%rowtype;
temp varchar2(25);
begin
for c1r in c1 loop
temp:=nvl(temp,' ')||c1r.status;
end loop;
return nvl(temp,' ');
dbms_output.put_line(nvl(temp,' '));
end;

To Return x,p,s Records then use:

SELECT key,seqno,status FROM (
SELECT key,seqno,status,rank()
OVER (ORDER BY seqno DESC) EMPRANK
FROM test_1)
WHERE emprank <=3;

[dowens]

The status codes are not constants.
The seqno could be =1 or > 1.
the table name is ChgDate.

MS2000 SQL

Very new to SQL!