Home » SQL & PL/SQL » SQL & PL/SQL » Advanced sorting
Advanced sorting [message #36669] Tue, 11 December 2001 04:18 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

----------------------------------------------------------------------
Previous Topic: Sending mail from PL/SQL procudure
Next Topic: Remove duplicate and leave max
Goto Forum:
  


Current Time: Thu Oct 29 21:28:30 CDT 2020