Home » SQL & PL/SQL » SQL & PL/SQL » order by the value from column
order by the value from column [message #238185] Thu, 17 May 2007 01:01 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
how do i order by the value from pk_std_id COLUMN IN SELECT STATEMENT
pk_std_id fk_name
1001 marks
1002 std
1003 student_name
1004 pass
1005 garde
1006 fail
1007 Comp
1008 Discontinued

select * from student order by pk_std_id
output
1001
1002
1003........
but i need ORDER BY should be ,FIRST RECORD SHOULD BE 1003,THEN 1002,THEN 1001,1004,1005,1007,1006,1008
thanxs
Re: order by the value from column [message #238187 is a reply to message #238185] Thu, 17 May 2007 01:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Try an order by in combination with DECODE:
ORDER BY DECODE( pk_std_id
               , 1003, 1
               , 1002, 2
               , 1001, 3
               , pk_std_id
               )


MHE
Re: order by the value from column [message #238194 is a reply to message #238187] Thu, 17 May 2007 01:26 Go to previous messageGo to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
But,

If I have thousands of records, the have I to write each choice in DECODE???????

It will be a very lenghty process..............

Prachi
Re: order by the value from column [message #238200 is a reply to message #238194] Thu, 17 May 2007 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But in this case someone already wrote somewhere the correct order for thousand values, doesn't it?
Do you think someone can define the order of thousand values without an algorithm?

Regards
Michel
Re: order by the value from column [message #238204 is a reply to message #238187] Thu, 17 May 2007 01:50 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
thanxs a lot maaher it worked
as parchi says its true but i will have only 12 pk_std_id its fixed
any thanxs for all of u
Re: order by the value from column [message #238208 is a reply to message #238194] Thu, 17 May 2007 02:02 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
SELECT * FROM number1 e ORDER BY CASE WHEN ROWNUM=1 THEN 3 
WHEN ROWNUM=3 THEN 1 
ELSE ROWNUM END 

thanks,
srinivas
Re: order by the value from column [message #238213 is a reply to message #238208] Thu, 17 May 2007 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
srinivas,

I think the wanted order is based on the values not on the row numbers in the result set.

Regards
Michel
Re: order by the value from column [message #238216 is a reply to message #238213] Thu, 17 May 2007 02:25 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
no michel this is swaping means interchange
means 1st row going to 2nd,
2nd row going to 1st.

SELECT e.* FROM EMP e  ORDER BY CASE WHEN ROWNUM=1 THEN 2 
WHEN ROWNUM=2 THEN 1 
ELSE ROWNUM END 



select e.*,rownum from emp e order by
case when rownum=(select max(rownum) from emp) then 1 
when rownum=1 then (select max(rownum) from emp)
else rownum end 

[Updated on: Thu, 17 May 2007 02:26]

Report message to a moderator

Re: order by the value from column [message #238222 is a reply to message #238216] Thu, 17 May 2007 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understand your query what I meant is the OP does not want the first row to swap with the second one or the like (actually it would be the third swaps the first) but the VALUE 1003 whould be first then comes the VALUE 1002 then the VALUE 1001 then in order of the VALUE.
There is no relation with the number of the rows returns by Oracle.
Your query actually returns rows in an undeterministic order.
As well as your last query.

Regards
Michel


Re: order by the value from column [message #238223 is a reply to message #238222] Thu, 17 May 2007 02:37 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

now is it clear.




SELECT * FROM number1
o/p
NO
1
2
3
4
5

SELECT * FROM number1  ORDER BY CASE WHEN ROWNUM=1 THEN 3 
WHEN ROWNUM=3 THEN 1 
ELSE ROWNUM END 

o/p

NO
3
2
1
4
5


[Updated on: Thu, 17 May 2007 02:39]

Report message to a moderator

Re: order by the value from column [message #238226 is a reply to message #238223] Thu, 17 May 2007 02:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
But, as you've been told on many occasions, without an Order by clause, there is no guarantee what order the rows will be returned in.
I've come to the conclusion that you just don't believe us when we tell you this, despite the various examples that have been posted, and the fact that the documentation clearly states it.

Why do you insist on posting queries that only work if the data comes out in a specific order, and not including order by statements?
Re: order by the value from column [message #238228 is a reply to message #238223] Thu, 17 May 2007 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not prove anything.
SQL> select * from number1;
        NO
----------
         1
         2
         3
         4
         5

5 rows selected.

SQL> SELECT * FROM number1  
  2  ORDER BY 
  3    CASE WHEN ROWNUM=1 THEN 3 
  4         WHEN ROWNUM=3 THEN 1 
  5         ELSE ROWNUM END 
  6  /
        NO
----------
         5
         2
         3
         1
         4

5 rows selected.

SQL> /
        NO
----------
         2
         3
         1
         5
         4

5 rows selected.

SQL> /
        NO
----------
         2
         3
         1
         5
         4

5 rows selected.

SQL> /
        NO
----------
         2
         3
         1
         5
         4

5 rows selected.

SQL> /
        NO
----------
         5
         2
         3
         1
         4

5 rows selected.

Regards
Michel
Re: order by the value from column [message #238234 is a reply to message #238222] Thu, 17 May 2007 02:58 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi

A very strange sequence she wants and that is not coming in this query

Quote:
select e.*,rownum from emp e
order by
case when rownum = (select max(rownum) from emp) then 1
when rownum=1 then (select max(rownum) from emp)
else rownum end


Quote:
FIRST RECORD SHOULD BE 1003,THEN 1002,THEN 1001,1004,1005,1007,1006,1008


@OP do you able to find the algorithm.
Ashu
Re: order by the value from column [message #238236 is a reply to message #238223] Thu, 17 May 2007 02:58 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

see michel iam not use any rownum in case statement.


SELECT * FROM number1 ORDER BY  CASE WHEN ROWNUM=1 THEN (SELECT 3 FROM dual)
                                          WHEN  NO=3   THEN  1
                                          ELSE NO END 

[Updated on: Thu, 17 May 2007 03:19]

Report message to a moderator

Re: order by the value from column [message #238237 is a reply to message #238236] Thu, 17 May 2007 02:59 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
@pavuluri: There's no hope for you. Sad


MHE

[Updated on: Thu, 17 May 2007 02:59]

Report message to a moderator

Re: order by the value from column [message #238244 is a reply to message #238236] Thu, 17 May 2007 03:14 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@pavuluri: keep your fingers away from the keyboard, look at the execution I posted and think, think, think.

ROWNUM means UNDETERMINISTIC unless there is an inner order

Regards
Michel
Previous Topic: Can you guyz check this code plz !!!
Next Topic: Sequence in SQL
Goto Forum:
  


Current Time: Mon Dec 05 06:48:04 CST 2016

Total time taken to generate the page: 0.06174 seconds