Home » SQL & PL/SQL » SQL & PL/SQL » Order by issue (SQL)
Order by issue [message #425166] Wed, 07 October 2009 21:44 Go to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi All,

I run a query
select 2,substr('"'||ss||'",'||
'"'||yy,1,100)||'"' from
(select sysdate ss, to_char(sysdate, 'YYYY') yy
from dual
union
select sysdate+60 ss, to_char(sysdate+60, 'YYYY') yy
from dual
union
select sysdate-60 ss, to_char(sysdate-60, 'YYYY') yy
from dual
order by 1 desc);

output :
2 SUBSTR('"'||SS||'"
--------- ------------------
2 "09-AUG-09","2009"
2 "08-OCT-09","2009"
2 "07-DEC-09","2009"

Then I union one more query along with this, to show label in excel, the query as follows...

select 1 seq,'Date, Year' lbl
from dual
UNION
select 2,substr('"'||ss||'",'||
'"'||yy,1,100)||'"' from
(select sysdate ss, to_char(sysdate, 'YYYY') yy
from dual
union
select sysdate+60 ss, to_char(sysdate+60, 'YYYY') yy
from dual
union
select sysdate-60 ss, to_char(sysdate-60, 'YYYY') yy
from dual
order by 1 desc);

output:
SEQ LBL
--------- ------------------
1 Date, Year
2 "07-DEC-09","2009"
2 "08-OCT-09","2009"
2 "09-AUG-09","2009"

The issue is : The date order is changed based on alphabetic for the lbl column, I wanted the output as shown below (not as above)...
SEQ LBL
--------- ------------------
1 Date, Year
2 "09-AUG-09","2009"
2 "08-OCT-09","2009"
2 "07-DEC-09","2009"

How to achieve? Thanks.

-Karthik
Re: Order by issue [message #425177 is a reply to message #425166] Wed, 07 October 2009 23:08 Go to previous messageGo to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

hi ap_karthi,

You can do it by make a little change in your query like
following,

Query:

select 1 seq,'Date, Year' lbl
from dual
UNION
select 2,substr('"'||ss||'",'||
'"'||yy,1,100)||'"' from
(select sysdate ss, to_char(sysdate, 'YYYY') yy
from dual
union
select sysdate+60 ss, to_char(sysdate+60, 'YYYY') yy
from dual
union
select sysdate-60 ss, to_char(sysdate-60, 'YYYY') yy
from dual
)
order by 1,2 desc
Re: Order by issue [message #425186 is a reply to message #425166] Wed, 07 October 2009 23:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Order by issue [message #425195 is a reply to message #425177] Thu, 08 October 2009 00:21 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
I tried your option, but failed.
I don't know, some how it is working with dual, but not with the table.

Please follow the steps...

1. create table test_order(ord_date date);

2. insert into test_order values('&d');

3. Inserted values are shown below,

SQL> SELECT * FROM TEST_ORDER;

ORD_DATE
---------
01-JAN-09
20-AUG-09
01-FEB-09
15-DEC-09
31-JUL-09

4. Run the given below query,

select 1 seq,'Date, Year' lbl
from dual
UNION
select 2,substr('"'||ss||'",'||
'"'||yy,1,100)||'"' from
(select trim(ord_date) ss, to_char(ord_date, 'YYYY') yy
from test_order
)
order by 1,2 desc

5. The output is showing as...
SEQ LBL
--------- ------------------
1 Date, Year
2 "31-JUL-09","2009"
2 "20-AUG-09","2009"
2 "15-DEC-09","2009"
2 "01-JAN-09","2009"
2 "01-FEB-09","2009"

6. But the output should be as follow,

SEQ LBL
--------- ------------------
1 Date, Year
2 "01-JAN-09","2009"
2 "01-FEB-09","2009"
2 "31-JUL-09","2009"
2 "20-AUG-09","2009"
2 "15-DEC-09","2009"
Re: Order by issue [message #425199 is a reply to message #425195] Thu, 08 October 2009 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted.
Still no test case.
Still no version.

Regards
Michel
Re: Order by issue [message #425200 is a reply to message #425195] Thu, 08 October 2009 00:42 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

when you are doing order by 1 its doing order by on the string which it is getting in the query.
In case you want order by the date. Put the same in the clause .

Regards
Prajakta

I have tried

SELECT   TO_CHAR (a, 'DD-MON-YYYY') || ',' || TO_CHAR (a, 'YYYY')
    FROM (SELECT TRUNC (SYSDATE) a
            FROM DUAL
          UNION
          SELECT TRUNC (SYSDATE) - 40 a
            FROM DUAL
          UNION
          SELECT TRUNC (SYSDATE) - 80 a
            FROM DUAL
          UNION
          SELECT TRUNC (SYSDATE) - 10 a
            FROM DUAL)
ORDER BY a



Regards
Prajakta001

[Updated on: Thu, 08 October 2009 00:43]

Report message to a moderator

Re: Order by issue [message #425201 is a reply to message #425200] Thu, 08 October 2009 00:43 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Sorry Michel
..I did not see your mail....

Regards
Prajakta001
Re: Order by issue [message #425219 is a reply to message #425166] Thu, 08 October 2009 01:53 Go to previous messageGo to next message
omaribais
Messages: 34
Registered: October 2009
Location: Bangladesh
Member

hi ap_karthi,

please try the following code,

Code:
select seq,lbl
from (
select 1 seq,'Date, Year' lbl,sysdate dt
from dual
UNION
select 2,(substr('"'||ss||'",'||
'"'||yy,1,100)||'"') lbl,dt from
(select sysdate ss, to_char(sysdate, 'YYYY') yy,sysdate dt
from dual
union
select sysdate+60 ss, to_char(sysdate+60, 'YYYY') yy,sysdate+60 dt
from dual
union
select sysdate-60 ss, to_char(sysdate-60, 'YYYY') yy,sysdate-60 dt
from dual)
order by 1,dt
)


or

select seq,lbl
from (
select 1 seq,'Date, Year' lbl,sysdate dt
from dual
UNION
select 2,(substr('"'||ss||'",'||
'"'||yy,1,100)||'"' )lbl,ord_date dt from
(select trim(ord_date) ss, to_char(ord_date, 'YYYY') yy,ord_date
from test_order
)
order by 1,dt
)


- Thanks
Re: Order by issue [message #425222 is a reply to message #425166] Thu, 08 October 2009 02:13 Go to previous messageGo to next message
MarkusW
Messages: 20
Registered: June 2009
Location: Austria
Junior Member
Hi ap_karthi,

when you change the order by clause out of the union selects, it will work.

select 1 seq,'Date, Year' lbl
from dual
union
select 2, substr('"'||ss||'",'|| '"'||yy,1,100)||'"' from
(
    select sysdate ss, to_char(sysdate, 'YYYY') yy
    from dual
    union
    select sysdate+60 ss, to_char(sysdate+60, 'YYYY') yy
    from dual
    union
    select sysdate-60 ss, to_char(sysdate-60, 'YYYY') yy
    from dual
)
order by 1,2 desc;


Result
SEQ	LBL
-------------------------
1	Date, Year
2	"09.08.09","2009"
2	"08.10.09","2009"
2	"07.12.09","2009"



Regards
Mark

[Updated on: Thu, 08 October 2009 06:31]

Report message to a moderator

Re: Order by issue [message #425387 is a reply to message #425219] Thu, 08 October 2009 22:04 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi Omar,

I used your latest query, but still the output shows as follows,

SEQ LBL
--------- ------------------
1 Date, Year
2 "01-FEB-09","2009"
2 "01-JAN-09","2009"
2 "15-DEC-09","2009"
2 "20-AUG-09","2009"
2 "31-JUL-09","2009"

Note: The above output considers the LBL as a string and sorts it

I wanted the output as...
SEQ LBL
--------- ------------------
1 Date, Year
2 "01-JAN-09","2009"
2 "01-FEB-09","2009"
2 "31-JUL-09","2009"
2 "20-AUG-09","2009"
2 "15-DEC-09","2009"


-Karthik

Re: Order by issue [message #425388 is a reply to message #425222] Thu, 08 October 2009 22:08 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi Mark,

Kindly refer above my reply to Omar's reply.
FYI,
I don't know, some how it is working with dual, but not with the table. The script is given above.

-Karthik

Re: Order by issue [message #425390 is a reply to message #425388] Thu, 08 October 2009 23:01 Go to previous message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
If the dates were displayed in YYYY-MM-DD format, they would sort as expected/desired.
Previous Topic: query audit table (merged 4)
Next Topic: Change code from oracle to sqlserver 2008
Goto Forum:
  


Current Time: Fri Sep 30 07:19:02 CDT 2016

Total time taken to generate the page: 0.09411 seconds