Home » SQL & PL/SQL » SQL & PL/SQL » order
order [message #254327] Thu, 26 July 2007 07:54 Go to next message
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 #254329 is a reply to message #254327] Thu, 26 July 2007 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you can but what is the meaning of such thing?

Regards
Michel
Re: order [message #254330 is a reply to message #254329] Thu, 26 July 2007 08:04 Go to previous messageGo to next message
vipindiwan
Messages: 7
Registered: July 2007
Location: INDIA
Junior Member

Sir,

this is the user demand he wants the values in this order

so plz help me

Re: order [message #254331 is a reply to message #254330] Thu, 26 July 2007 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #254530 is a reply to message #254520] Fri, 27 July 2007 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't tell us what you tried.

Regards
Michel
Re: order [message #254536 is a reply to message #254327] Fri, 27 July 2007 01:21 Go to previous messageGo to next message
markireland
Messages: 5
Registered: July 2007
Location: Australia
Junior Member
So

order by NAME ASC, DEPTNO DESC does not work?
Re: order [message #254537 is a reply to message #254536] Fri, 27 July 2007 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have 2 independent orders, you have to use 2 queries.

Regards
Michel
Re: order [message #254542 is a reply to message #254537] Fri, 27 July 2007 01:31 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It is very dangerous to try to do this. You lose all reference between the columns. But like Michel said: you have two INDEPENDENT orders.

MHE
Re: order [message #254555 is a reply to message #254542] Fri, 27 July 2007 02:07 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #254571 is a reply to message #254569] Fri, 27 July 2007 02:48 Go to previous messageGo to next message
vipindiwan
Messages: 7
Registered: July 2007
Location: INDIA
Junior Member

thanks dharam !!!!
it works...
Re: order [message #254572 is a reply to message #254569] Fri, 27 July 2007 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.
If you want to ask, read How to get a quick answer to your question: TIPS AND TRICKS and always post your Oracle version (4 decimals).

Regards
Michel

Re: order [message #254573 is a reply to message #254569] Fri, 27 July 2007 02:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
dharam wrote on Fri, 27 July 2007 09:44

You can do it
Yes he can, but what about data integrity?

MHE
Re: order [message #254581 is a reply to message #254573] Fri, 27 July 2007 03:41 Go to previous messageGo to next message
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 #254584 is a reply to message #254581] Fri, 27 July 2007 03:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
dharam wrote on Fri, 27 July 2007 10:41
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
http://www.orafaq.com/forum/fa/449/0/ Let's blame the database. What error should it return? ORA-600 [SILLY REQUEST]?

No, I beg to differ. Programmers, developers and analysts are responsible for design flaws. Oracle cannot be blamed for offering flexibility. I just think that row wise integrity is pretty important in a database. That's a personal opinion, of course.

But hey, it's not my party. Any query like that would not pass easily at our shop. That's for sure.

MHE
Re: order [message #254585 is a reply to message #254581] Fri, 27 July 2007 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dharam,

Maarten does not talk about data integrity inside the database but data integrity for clients.
You show them on the same line 2 data that have no relation.

What do you think about the following output?
ENAME             SAL
---------- ----------
ADAMS            5000
ALLEN            3000
BLAKE            3000
CLARK            2975
FORD             2850
JAMES            2450
JONES            1600
KING             1500
MARTIN           1300
MILLER           1250
SCOTT            1250
SMITH            1100
TURNER            950
WARD              800

Regards
Michel
Re: order [message #254590 is a reply to message #254585] Fri, 27 July 2007 04:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #254599 is a reply to message #254590] Fri, 27 July 2007 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is: if someone shows you the previous report what do you think?
For myself, I'd think that it is better to have a name beginning with A.

Regards
Michel
Re: order [message #254607 is a reply to message #254593] Fri, 27 July 2007 04:39 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Wink. 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 Very Happy.

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Wink), 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 Go to previous messageGo to next message
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 #254637 is a reply to message #254635] Fri, 27 July 2007 05:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
dharam wrote on Fri, 27 July 2007 12:49
You raised about data integrity.
I meant the integrity of the data set. I thought that was implied.

dharam wrote on Fri, 27 July 2007 12:49
If you feel about data integrity in these cases.Then my opinions are those................
You lost me here. I have absolutely no idea what you are talking about. Sorry.

MHE
Re: order [message #254642 is a reply to message #254635] Fri, 27 July 2007 06:13 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: Is It possible to use Indexes in select,insert ,update situations.
Next Topic: trigger problem
Goto Forum:
  


Current Time: Sun Dec 11 00:27:24 CST 2016

Total time taken to generate the page: 0.09991 seconds