| order [message #254327] |
Thu, 26 July 2007 07:54  |
vipindiwan
Messages: 7
Registered: July 2007
Location: INDIA
|
Junior Member |
|
|
can i make asc & desc order in a table with two column completely
suppose i have table
NAME DEPTNO
A 4
C 1
D 2
E 3
B 5
THE OUT PUT SHOULD BE
NAME DEPTNO
A 5
B 4
C 3
D 2
E 1
|
|
|
|
|
|
|
|
| Re: order [message #254331 is a reply to message #254330] |
Thu, 26 July 2007 08:11  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I ask you I don't see any reason, so tell me to see if giving such an answer cannot lead to error for others.
By the way, what did you try or think till now?
Regards
Michel
[Updated on: Thu, 26 July 2007 08:11] Report message to a moderator |
|
|
|
| Re: order [message #254520 is a reply to message #254331] |
Fri, 27 July 2007 00:51 |
vipindiwan
Messages: 7
Registered: July 2007
Location: INDIA
|
Junior Member |
|
|
hi ,
i m working in oracle based software my senior & me are working on it,this is the user demand he wants two column in the report out put like this,
when i did one column is asc or desc but second column is always
corresponding to first one,
if u can help me plz help me in this matter or this is not possible????
|
|
|
|
|
|
|
|
|
|
|
|
| Re: order [message #254555 is a reply to message #254542] |
Fri, 27 July 2007 02:07 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
To make it even clearer in case you still don't get it:
The values A and 4 are not in the same record by coincidence. It is so, because they are related. A and 5 on the other hand, are not related.
So, either you oversimplified the question, and the two values don't come from a single tuple, or the requirement sucks.
|
|
|
|
| Re: order [message #254569 is a reply to message #254327] |
Fri, 27 July 2007 02:44 |
dharam
Messages: 6
Registered: July 2007
Location: INDIA
|
Junior Member |
|
|
Hi, vipindiwan
You can do it , Use this query.............
select b.name,a.deptno from (select rownum rm,deptno from (select deptno from test order by deptno desc )) a,
(select rownum rmm,name from (select name from test order by name )) b
where a.rm=b.rmm;
********out put is ***********
NAME DEPTNO
---------- ----------
A 5
B 4
C 3
D 2
E 1
|
|
|
|
|
|
|
|
|
|
| Re: order [message #254581 is a reply to message #254573] |
Fri, 27 July 2007 03:41 |
dharam
Messages: 6
Registered: July 2007
Location: INDIA
|
Junior Member |
|
|
Dear MHE
This type of intigrity must be maintain by Oracle Corporation.
It is the fault of Oracle......,and you see, user can break the intigrity
|
|
|
|
|
|
|
|
| Re: order [message #254590 is a reply to message #254585] |
Fri, 27 July 2007 04:18 |
dharam
Messages: 6
Registered: July 2007
Location: INDIA
|
Junior Member |
|
|
hi,
Without update the table,without taken manual data in query, it is possible.So, it depends on you "what should you think".It is the problem of database integrity, or others........
|
|
|
|
| Re: order [message #254593 is a reply to message #254584] |
Fri, 27 July 2007 04:21 |
dharam
Messages: 6
Registered: July 2007
Location: INDIA
|
Junior Member |
|
|
Dear MHE
Just be cool,I have no pre-intention to blame Oracle, But dear think deeply in logical aspect.....
|
|
|
|
|
|
| Re: order [message #254607 is a reply to message #254593] |
Fri, 27 July 2007 04:39 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
| dharam wrote on Fri, 27 July 2007 11:21 | Dear MHE
Just be cool,I have no pre-intention to blame Oracle, But dear think deeply in logical aspect.....
|
I am cool, don't worry  . I can appreciate a bit of discussion from time to time. After all this is a discussion forum.
Oracle has its flaws but you cannot blame the database if you mess with your queries. Since you brought up logics: I like a coherent result set. If I fetch a record, it is assuring to know that the data in the columns belongs to the same record in the same database. If you can't count on that, you're just fetching random tuples. I think that's logical.
But like I said before: it's not my party. Actually I'm glad I'm not invited  .
MHE
Edit: when you think about it, it is a matter of semantics: what does the data MEAN? With a query like that, the data has lost its meaning: you have a name and it hapens to be alongside a deptno. Does this deptno have any relationship with the name? I couldn't tell. What's the use of meaningless data? And that was the point we were trying to make. It is useless and not the brightest idea.
[Updated on: Fri, 27 July 2007 04:48] Report message to a moderator |
|
|
|
| Re: order [message #254620 is a reply to message #254599] |
Fri, 27 July 2007 05:11 |
dharam
Messages: 6
Registered: July 2007
Location: INDIA
|
Junior Member |
|
|
I agree with you,but sir, Programmers, developers and analysts manage all the things even they find the bugs in.Oracle always talks about 100% data integrity.But where is............? This type of activities must be forced by Oracle, not to do.This is my personal opinion.
regards
dharam
|
|
|
|
| Re: order [message #254628 is a reply to message #254620] |
Fri, 27 July 2007 05:30 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
| Quote: |
This type of activities must be forced by Oracle, not to do
|
The problem is, the only thing Oracle can possible know is that there is a varchar field and a number field with some data in it.
The only one who is (should) be able judge if it makes SENSE to query the data in such a way is the one who writes the query.
This kind of query could be perfectly logical and valid if for example it's not name / deptno but maybe "parking lot" / "office number" in an employee table, and the report is just supposed to give an list of all occupied parking lots and all occupied office numbers, both ordered.
|
|
|
|
| Re: order [message #254629 is a reply to message #254620] |
Fri, 27 July 2007 05:32 |
|
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
| dharam wrote on Fri, 27 July 2007 12:11 | This type of activities must be forced by Oracle
|
Are you serious? If you select from a table, your result set is consistent. If you select two times from the same table, with nothing to link the two subsets but some order you gave (and order has no true meaning ), it is your job to provide a proper join condition. It is a design flaw from the user.
Or would you like Oracle to apply natural joins by default? I sure hope you would report this as a bug to Oracle. I like a good laugh.
MHE
[Updated on: Fri, 27 July 2007 05:32] Report message to a moderator |
|
|
|
| Re: order [message #254635 is a reply to message #254629] |
Fri, 27 July 2007 05:49 |
dharam
Messages: 6
Registered: July 2007
Location: INDIA
|
Junior Member |
|
|
Dear MHE
I know well, it is meaning less data.I just replied the question raised by vipindiwan.You raised about data integrity.so discussion was going on.If you feel about data integrity in these cases.Then my opinions are those................
|
|
|
|
|
|
| Re: order [message #254642 is a reply to message #254635] |
Fri, 27 July 2007 06:13 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
So, dharam, if I follow your logic and take it one step further, it is the databases fault that the following is possible:
create two tables with a foreign key between them
disable the foreign key
insert data in child-table
Basically, what you are saying is that the database should take care of the integrity, regardless of the fact that I explicitly told it NOT to do so.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
