| Advanced sorting [message #36669] |
Tue, 11 December 2001 04:18  |
Henrik Thomsen
Messages: 4
Registered: December 2001
|
Junior Member |
|
|
Hi,
I have data like this
- Fld1 - - Fld2 -
- ---- - - ---- -
- 3 - - H -
- 7 - - A -
- 5 - - J -
- 10 - - K -
I would like to make a query that returns a single row
- Val1 - - Val2 - - Val3 - - Val4 -
- ---- - - ---- - - ---- - - ---- -
- H - - J - - A - - K -
Where Val1 is H because it has the lowest Fld1 (3), Val2 is second because it has the second lowest Fld1 (5), ... and Val4 is K because Fld1 is highest (10).
How can I construct such an SQL-query (perhaps by the use of PL/SQL)?
----------------------------------------------------------------------
|
|
|
|
| Re: Advanced sorting [message #36674 is a reply to message #36669] |
Tue, 11 December 2001 11:08  |
usha
Messages: 20
Registered: December 2000
|
Junior Member |
|
|
Is the first column a character or numeric field?? If it is a number try using to_char function.
----------------------------------------------------------------------
|
|
|
|
| Re: Advanced sorting [message #36678 is a reply to message #36669] |
Tue, 11 December 2001 23:12 |
Henrik Thomsen
Messages: 4
Registered: December 2001
|
Junior Member |
|
|
Hi,
The first coloumn is integer. The tricky part is that it is not certain that the table contains 4 rows, but perhaps only three or two.
My output should still be 4 columns in a single row, that show field 2 sorted by field 1.
I cannot se what I should do with to_char. Can you please explain...
----------------------------------------------------------------------
|
|
|
|
| Re: Advanced sorting [message #36682 is a reply to message #36669] |
Wed, 12 December 2001 01:22 |
smk
Messages: 5
Registered: November 2001
|
Junior Member |
|
|
Hi, I have tested this code, do try it out hope it give the result required.
declare
cursor c1 is
select * from test
order by fld1;
text varchar2(50);
begin
for i in c1 loop
text := text||i.fld2||' ';
end loop;
dbms_output.put_line(text);
end;
/
Do let me know if it works out or not.
Bye
Sanjay
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
