Home » SQL & PL/SQL » SQL & PL/SQL » order by
order by [message #273587] Thu, 11 October 2007 01:04 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello all,

my queries is like

SELECT SUBSTR(VC_PROF_INV_NO,3) proforma_no,VC_PROF_INV_NO,
TO_CHAR(DT_PROF_DATE,'dd-mm-yyyy') proforma_date ,
vc_company_name FROM export.HD_PROFORMA_INV a , mst_company b
WHERE a.vc_comp_code ='01' AND
b.vc_comp_code = SUBSTR(VC_PROF_INV_NO,1,2)
ORDER BY proforma_date ,proforma_no

but i m not getting data in an order.

please advise me how will i do??


1	011	05-10-2007	SHARDA EXPORTS HARDWAR
2	012	08-10-2007	SHARDA EXPORTS HARDWAR
3	013	08-10-2007	SHARDA EXPORTS HARDWAR
4	014	09-10-2007	SHARDA EXPORTS HARDWAR
5	015	09-10-2007	SHARDA EXPORTS HARDWAR
6	016	09-10-2007	SHARDA EXPORTS HARDWAR
7	017	09-10-2007	SHARDA EXPORTS HARDWAR
10	0110	10-10-2007	SHARDA EXPORTS HARDWAR
11	0111	10-10-2007	SHARDA EXPORTS HARDWAR
12	0112	10-10-2007	SHARDA EXPORTS HARDWAR
13	0113	10-10-2007	SHARDA EXPORTS HARDWAR
14	0114	10-10-2007	SHARDA EXPORTS HARDWAR
8	018	10-10-2007	SHARDA EXPORTS HARDWAR
9	019	10-10-2007	SHARDA EXPORTS HARDWAR
 


regds
Re: order by [message #273592 is a reply to message #273587] Thu, 11 October 2007 01:10 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
In your statement, you have:
ORDER BY proforma_date ,proforma_no

Since proforma_no (as far as I can see) is not an actual column but something you calculate, I would have expected you to get an ORA-00904 (invalid identifier).

Anyway, if you want to order on that calculated item, you have to change it to a number (please note that strictly speaking SQL is doing exactly what you ask for: it has sorted that column alphabetically). So, change it to a number, at least in the order by, but I would recommend to do it in the select too, in case you are going to use it in some other code.
Re: order by [message #273603 is a reply to message #273592] Thu, 11 October 2007 01:21 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
skooman wrote on Thu, 11 October 2007 08:10

Since proforma_no (as far as I can see) is not an actual column but something you calculate, I would have expected you to get an ORA-00904 (invalid identifier).
Sabine, as the ORDER BY is executed AFTER the fetch (even after ROWNUM IS assigned), Oracle has the information (and aliases) to sort. No ORA messages are expected.
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select rownum
  2       , reverse(loc) rubbish_2
  3  from   dept
  4  order by rubbish_2
  5  /

    ROWNUM RUBBISH_2
---------- -------------
         4        NOTSOB
         2        SALLAD
         3       OGACIHC
         1      KROY WEN

But for the rest: I agree. If you want a numeric sort, make sure that the column you use to sort is numeric. Simple.

MHE
Re: order by [message #273607 is a reply to message #273587] Thu, 11 October 2007 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query also needs to be formatted.

I can see the result is in the order you specified.
Maybe not the one you want but the one you gave.
Now say what you want.

Regards
Michel
Re: order by [message #273614 is a reply to message #273607] Thu, 11 October 2007 01:35 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
i have changed my query..


SELECT VC_PROF_INV_NO,SUBSTR(VC_PROF_INV_NO,3) proforma_no,
TO_CHAR(DT_PROF_DATE,'dd-mm-yyyy') proforma_date ,
vc_company_name FROM export.HD_PROFORMA_INV a , mst_company b
WHERE a.vc_comp_code ='01' AND
b.vc_comp_code = SUBSTR(VC_PROF_INV_NO,1,2)
ORDER BY VC_PROF_INV_NO

result is like this, now tell me how will i do..

011	1	05-10-2007	SHARDA EXPORTS HARDWAR
0110	10	10-10-2007	SHARDA EXPORTS HARDWAR
0111	11	10-10-2007	SHARDA EXPORTS HARDWAR
0112	12	10-10-2007	SHARDA EXPORTS HARDWAR
0113	13	10-10-2007	SHARDA EXPORTS HARDWAR
0114	14	10-10-2007	SHARDA EXPORTS HARDWAR
012	2	08-10-2007	SHARDA EXPORTS HARDWAR
013	3	08-10-2007	SHARDA EXPORTS HARDWAR
014	4	09-10-2007	SHARDA EXPORTS HARDWAR
015	5	09-10-2007	SHARDA EXPORTS HARDWAR
016	6	09-10-2007	SHARDA EXPORTS HARDWAR
017	7	09-10-2007	SHARDA EXPORTS HARDWAR
018	8	10-10-2007	SHARDA EXPORTS HARDWAR
019	9	10-10-2007	SHARDA EXPORTS HARDWAR
 


regards
Re: order by [message #273616 is a reply to message #273603] Thu, 11 October 2007 01:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Maaher wrote on Thu, 11 October 2007 08:21

If you want a numeric sort, make sure that the column you use to sort is numeric. Simple.
One hint: to_number. But then again, we can only guess. You never stipulated how you expect the records to be sorted.

MHE

[Updated on: Thu, 11 October 2007 01:44]

Report message to a moderator

Re: order by [message #273625 is a reply to message #273614] Thu, 11 October 2007 02:10 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you still didn't format your query.

Regards
Michel
Previous Topic: Outer Join Question
Next Topic: Can we use case stmt in update stmt
Goto Forum:
  


Current Time: Mon Dec 05 19:16:05 CST 2016

Total time taken to generate the page: 0.17992 seconds