take one table is t1 and in that column name is f1
f1 column values are
200
5000
3000
7000
300
600
100
400
800
400
i want display the values asc and desc in a single output.
sample output is
f1.a
100
200
300
400
500
600
etc......
and f1.d is
5000
4000
3000
2000
1000
etc...
Answers were Sorted based on User's Feedback
Answer / sumathy
Create table as 'f_order' with coulmn f1.
Run the following statement to get the above quesion's
results.
DECLARE @a INT, @b int
DECLARE @f1 CURSOR, @f2 CURSOR
Print 'Ascending '
SET @f1 = CURSOR FOR
SELECT f1
FROM f_order order by 1 asc
OPEN @f1
FETCH NEXT
FROM @f1 INTO @a
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @a
FETCH NEXT
FROM @f1 INTO @a
END
CLOSE @f1
Print 'Descending '
SET @f2 = CURSOR FOR
SELECT f1
FROM f_order order by 1 desc
OPEN @f2
FETCH NEXT
FROM @f2 INTO @b
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @b
FETCH NEXT
FROM @f2 INTO @b
END
CLOSE @f2
DEALLOCATE @f1
DEALLOCATE @f2
Is This Answer Correct ? | 5 Yes | 2 No |
Answer / suresh
declare
type xyz is ref cursor return t1%rowtype;
c1 xyz;
e t1%rowtype;
begin
open c1 for select f1 from t1 order by f1 asc;
dbms_output.put_line('ascending order result');
dbms_output.put_line('---------------------');
loop
fetch c1 into e;
exit when c1%notfound;
dbms_output.put_line(e.f1);
end loop;
dbms_output.put_line('---------------------');
dbms_output.put_line('descending order result');
dbms_output.put_line('---------------------');
close c1;
open c1 for select f1 from t1 order by f1 desc;
loop
fetch c1 into e;
exit when c1%notfound;
dbms_output.put_line(e.f1);
end loop;
close c1;
end;
Is This Answer Correct ? | 5 Yes | 2 No |
Answer / innaci
select a,d from (select a,d,rownum as n from (select f1.a,
f2.d from
(select f1 as a from sample1 order by 1) f1,
(select f1 as d from sample1 order by 1 desc) f2) temp)
temp1 where mod(n,(select count(f1) from sample1)+1)=1
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / anjum
select * from (select f1 from t1 order by f1 asc)
union all
select * from (select f1 from t1 order by f1 desc)
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / pooja gupta
select f1.a, f2.d from
(select f1 as a from t order by 1) f1,
(select f1 as d from t order by 1 desc)f2
Is This Answer Correct ? | 2 Yes | 3 No |
Answer / venkyhulk1
if the output need to be displayed in two columns f1.a , f1.b
the querry will be
select a.f1 "f1.a", c.f1 "f1.b"
from (select rownum r1, f1 from t1) a,(select rownum r1,b.f1
f1 from (select f1 from t1 order by f1 desc) b) c
where a.r1=c.r1
hope this is the shortest querry that one can write in sql
to get this task done .
if u try it in other ways as shown above it will perform
catesian product which results in more rows than required .
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / sowmya
select a.f1 f1.a,b.f1 f1.b
from t1 a t1 b
order by a.f1 asc,b.f1 desc;
Is This Answer Correct ? | 1 Yes | 3 No |
Answer / monika
if u want to display output in two columns like f1.a,f1.b
then use
select * from(select f1 from t1)a,
select f1 from tt order by f1 desc)b);
if want to display output in same column then use union
like-
select f1 from t1
union
select f1 from t1 order by f1 desc;
Is This Answer Correct ? | 1 Yes | 3 No |
Answer / jitendra
#The ORDER BY keyword is used to sort the result-set by a #specified column.
#The ORDER BY keyword sort the records in ascending order by #default.
#If you want to sort the records in a descending order, you #can use the DESC keyword.
#1. display the values ascending order
SELECT f1 FROM t1 ORDER BY f1 AS f1.a
#2. display the values descending order
SELECT f1 FROM t1 ORDER BY f1 DESC AS f1.d
Is This Answer Correct ? | 0 Yes | 15 No |
What is a table partition?
What is oracle sql called?
Any attempt to navigate programmatically to disabled form in a call_form stack is allowed?
Differentiate between % rowtype and type record.
What is an inner join sql?
What is the difference between Union and Union all. Which is faster.
I have 2 Databases. How can create a table in particular database? How can i know the list of tables presented each database?( in oracle 10g)
Mention what plvcmt and plvrb does in pl/sql?
How to place comments in pl/sql?
What does select top 1 do in sql?
How much does sql certification cost?
using subquery how can i calculate working days in a month?