Home » SQL & PL/SQL » SQL & PL/SQL » The problem of use Order By (Oracle 10g)
The problem of use Order By [message #571219] Thu, 22 November 2012 04:15 Go to next message
Rasol_57
Messages: 18
Registered: November 2012
Location: India
Junior Member
Hi

I have an oracle query as follows :


SELECT A.Id,A.Attachment,A.CreateDateTime,A.No ,rownum as RowNumber
FROM (
       SELECT A.Id,A.Attachment,A.CreateDateTime,A.No FROM GOutgoingLetter A ,
       (
              SELECT B.Id, B.createdatetime as of0  FROM GOutgoingLetter B
              WHERE 
                      Exists
                     (Select BC.id
                      From XCase BC, GOutgoingLetterCase BG
                      Where
                      --    BG.CaseMechanizeState=1 And
                          B.id=BG.GOutgoingLetterId 
                     --    And BG.RelatedCaseID=BC.Id
                   --   And BC.LASTEMPEXPERTMANID='EF38398F7291491898AF63AC024  9DDBF'
                      )
   --           OR     A.RegistrarUserId =   '392182A64BD5499685FFAA2E570344D9'
       )B
       WHERE A.Id = B.Id
       ORDER BY B.of0
       )A



In the final ouput 'Attachment' and 'No' fields have no values but when ignore Order By clause , the above fields have values , why this happen ?

[Updated on: Thu, 22 November 2012 04:16]

Report message to a moderator

Re: The problem of use Order By [message #571222 is a reply to message #571219] Thu, 22 November 2012 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59088
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It is not clear what you have and get
2/ Copy and paste your SQL*Plus session
3/ Post a test case we can reproduce

Regards
Michel
Re: The problem of use Order By [message #571247 is a reply to message #571222] Thu, 22 November 2012 07:53 Go to previous messageGo to next message
Rasol_57
Messages: 18
Registered: November 2012
Location: India
Junior Member
Hi Michel

The above query Returns following results :

http://oracle2.persiangig.com/1.png

but when ignore 'order by' caluse the query return following results :

http://oracle2.persiangig.com/2.png

As Regards that the role of 'order by' clause is grouping records why the results different ?


Regards

nmacan

[Updated on: Thu, 22 November 2012 07:53]

Report message to a moderator

Re: The problem of use Order By [message #571248 is a reply to message #571247] Thu, 22 November 2012 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's not all of the results is it?
You're using PL/SQL Developer. That can, depending on preferences get some of the data from a query and then stop, until you tell it to get the rest of the data. I suggest you tell it to fetch all of the data, sort it in the grid (click on the column headings) and see if the data is in fact the same - it will be.
Re: The problem of use Order By [message #571249 is a reply to message #571248] Thu, 22 November 2012 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Though I should obviously point out that since you have rownum in the outer query the query with the order by will have different values for that column to the one without.
More precisely they'll have the same set of values but those values will be attached to different rows.
Re: The problem of use Order By [message #571251 is a reply to message #571247] Thu, 22 November 2012 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59088
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 22 November 2012 11:32
1/ It is not clear what you have and get
2/ Copy and paste your SQL*Plus session
3/ Post a test case we can reproduce

Regards
Michel


And I add: use SQL*Plus.

Regards
Michel
Re: The problem of use Order By [message #571264 is a reply to message #571219] Thu, 22 November 2012 11:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
namacan wrote on Thu, 22 November 2012 05:15
In the final ouput 'Attachment' and 'No' fields have no values but when ignore Order By clause , the above fields have values , why this happen ?


Most likely nothing. Without order by rows are returned in no particular order of createdatetime so you see non-null attachment and no columns here and there on first screen. When you add order by createdatetime rows returned on first screen have null attachment and no columns. So scroll down to screen where createdatetime is 1390/11/23-11:38 and you'll find what you are looking for.

SY.
Re: The problem of use Order By [message #571266 is a reply to message #571264] Thu, 22 November 2012 12:54 Go to previous messageGo to next message
Rasol_57
Messages: 18
Registered: November 2012
Location: India
Junior Member
Thanks a lot

My problem solved .

Re: The problem of use Order By [message #571320 is a reply to message #571266] Fri, 23 November 2012 05:29 Go to previous messageGo to next message
Rasol_57
Messages: 18
Registered: November 2012
Location: India
Junior Member
Hi

When i add 'Rownum' in the query with the 'order by' cluse the Problem solved .

However i dont now why ?


SELECT A.Id,A.Attachment,A.CreateDateTime,A.No ,rownum as RowNumber
FROM (
       SELECT A.Id,A.Attachment,A.CreateDateTime,A.No FROM GOutgoingLetter A ,
       (
              SELECT B.Id, B.createdatetime as of0 ,RowNum  FROM GOutgoingLetter B
              WHERE
                      Exists
                     (Select BC.id
                      From XCase BC, GOutgoingLetterCase BG
                      Where
                      --    BG.CaseMechanizeState=1 And
                          B.id=BG.GOutgoingLetterId 
                     --    And BG.RelatedCaseID=BC.Id
                   --   And BC.LASTEMPEXPERTMANID='EF38398F7291491898AF63AC024  9DDBF'
                      )
   --           OR     A.RegistrarUserId =   '392182A64BD5499685FFAA2E570344D9'
       )B
       WHERE A.Id = B.Id
       ORDER BY B.of0
       )A

Re: The problem of use Order By [message #571324 is a reply to message #571320] Fri, 23 November 2012 06:08 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since all really know about your problem is that you are getting stuff in a different order it's hard to say.
We really need more information on what the query is supposed to do.

However - if you always want the data in a consistent order then the order by should be on the outer query.
Re: The problem of use Order By [message #571449 is a reply to message #571219] Mon, 26 November 2012 01:53 Go to previous messageGo to next message
tony123
Messages: 9
Registered: August 2012
Junior Member

this may be a bug 4604970 of oracle10gR2.
first: alter session|system set "_gby_hash_aggregation_enabled"=false;
then:try your sql
Re: The problem of use Order By [message #571458 is a reply to message #571449] Mon, 26 November 2012 02:57 Go to previous message
Michel Cadot
Messages: 59088
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
first


WRONG! First ask the support BEFORE setting any underscore parameter.

Regards
Michel
Previous Topic: merge rows
Next Topic: to get the results in the form of a tree
Goto Forum:
  


Current Time: Wed Sep 17 01:32:12 CDT 2014

Total time taken to generate the page: 0.13409 seconds