| help [message #17823] |
Tue, 01 February 2005 14:24  |
zach
Messages: 7
Registered: January 2005
|
Junior Member |
|
|
could u help figure out what's wrong with the following codes?
UPDATE student
SET rowcounter = rownum
WHERE snumber IN (SELECT snumber FROM student WHERE snumber < '11324' ORDER BY snumber);
It says: ORA-00907: missing right parenthesis
If the ORDER BY is deleted, it works fine. but I need to order it by snumber though.
|
|
|
|
| Re: help [message #17824 is a reply to message #17823] |
Tue, 01 February 2005 14:50  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The ORDER BY clause is not valid in that subquery.
Storing order in the database - bad idea. You can always generate a "rowcounter" at runtime using either an analytical function or an inline view with rownum.
|
|
|
|
| Re: help [message #17828 is a reply to message #17823] |
Tue, 01 February 2005 20:38 |
Sreedhar Reddy
Messages: 55
Registered: January 2002
|
Member |
|
|
distinct keyword will bydefault order the snumber column
UPDATE student
SET rowcounter = rownum
WHERE snumber IN (SELECT distinct snumber FROM student WHERE snumber < '11324' );
|
|
|
|
| Re: help [message #17829 is a reply to message #17828] |
Tue, 01 February 2005 21:11 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
The ordering in the subquery has no effect AT ALL:
the in-clause means snumber should be 1 or 2 or 3. This does not differ from an unordered list: snumber should be 3 or 1 or 2.
So, there is absolutely no need for the order by.
hth
|
|
|
|
| Re: help [message #267261 is a reply to message #17829] |
Wed, 12 September 2007 21:07 |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
i also faced the same problem while using follwing query
select ename,sal from emp where sal in(select losal from salgrade order by losal desc where rownum<=2)
i wanted the name of employees from emp whose salary lies in two highest losal values from salgrade...
please tell how to do as oracle gives following error..
select ename,sal from emp where sal in(select losal from salgrade order by losal where rownum<=2)
*
ERROR at line 1:
ORA-00907: missing right parenthesis
thanks a lot
ashish
hi friends.. ca we do the above query using joins.........
plz answer
thanks
[Updated on: Thu, 13 September 2007 11:59] Report message to a moderator |
|
|
|
| Re: help [message #267267 is a reply to message #17824] |
Wed, 12 September 2007 22:29 |
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
|
Senior Member |
|
|
| Quote: |
select ename,sal from emp where sal in(select losal from salgrade order by losal where rownum<=2)
*
ERROR at line 1:
ORA-00907: missing right parenthesis
|
Answer is
| Todd Barry wrote on Tue, 01 February 2005 14:50 | The ORDER BY clause is not valid in that subquery.
|
|
|
|
|
| Re: help [message #267270 is a reply to message #267261] |
Wed, 12 September 2007 22:37 |
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
|
Senior Member |
|
|
| ashish2345 wrote on Wed, 12 September 2007 21:07 |
i wanted the name of employees from emp whose salary lies in two highest losal values from salgrade...
|
For find out salaries lies in higest losal you have to use between.
Try with the following
SELECT ename, sal
FROM emp, (SELECT MAX (losal) maxx, MIN (losal) minx
FROM (SELECT RANK () OVER (ORDER BY losal DESC) rn, losal
FROM salgrade)
WHERE rn <= 2) sal_ran
WHERE sal BETWEEN sal_ran.minx AND sal_ran.maxx
|
|
|
|
|
|
| Re: help [message #267339 is a reply to message #267261] |
Thu, 13 September 2007 01:29 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| ashish2345 wrote on Thu, 13 September 2007 04:07 |
select ename,sal from emp where sal in(select losal from salgrade order by losal where rownum<=2)
|
Look at the order of the elements of your subquery, first order by, then where.
That's an error, no matter if the order by would be accepted or not.
|
|
|
|
| Re: help [message #267509 is a reply to message #267339] |
Thu, 13 September 2007 10:08 |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
1 select ename,sal from emp
2 where sal in
3* (select losal from salgrade where rownum<=2 order by losal)
4 /
(select losal from salgrade where rownum<=2 order by losal)
*
ERROR at line 3:
ORA-00907: missing right parenthesis
i tried above too.. so it can be done by rank function. thanks alot buddies. i am really indebted by reponses. thanks again.day by day i am getting sql with gretater interest
[Updated on: Thu, 13 September 2007 12:23] by Moderator Report message to a moderator |
|
|
|
| Re: help [message #267564 is a reply to message #267509] |
Thu, 13 September 2007 13:09 |
TLegend33
Messages: 203
Registered: March 2005
|
Senior Member |
|
|
select ename,sal from emp
where sal in
(select losal from salgrade where rownum<=2)
order by sal;
You want to order by sal, correct?
|
|
|
|
| Re: help [message #267573 is a reply to message #267564] |
Thu, 13 September 2007 13:43 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | You want to order by sal, correct?
|
No he wants only the employees with the 2 first salaries.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
