Home » SQL & PL/SQL » SQL & PL/SQL » Order by clause (Oracle 10g)
| Order by clause [message #629071] |
Wed, 03 December 2014 02:21  |
 |
sss111ind
Messages: 634
Registered: April 2012
Location: India
|
Senior Member |
|
|
Hi All,
The order of the query should not change at any point of time if so it will result a complete error in the report.
For that whether I have to add any order by cluse at the last or this is enough . Please suggest.
WITH temp AS
(SELECT ROWNUM RN,particular,hiredate,sal,comm,
SUM(balance + sal - comm) OVER(ORDER BY hiredate NULLS FIRST) balance
FROM (SELECT 'OPEN' particular,to_date('') hiredate,0 sal,0 comm,344 balance
FROM dual
UNION ALL
SELECT ename,hiredate,NVL(sal, 0) sal,NVL(comm, 0) comm,0 balance
FROM emp2
ORDER BY hiredate nulls FIRST))
SELECT *
FROM temp
UNION ALL
SELECT MAX(rn) + 1 rn,'TOTAL' particular,to_date('') hiredate,
SUM(BALANCE) sal,SUM(COMM) comm,0 balance
FROM temp
UNION ALL
SELECT MAX(rn) + 2 rn,'FINAL' particular,to_date('') hiredate,
0 sal,0 comm,min (balance) balance
FROM temp
WHERE rn=(select max(rn) from temp);
-- order by rn
Regards,
Nathan
[Updated on: Wed, 03 December 2014 02:24] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Order by clause [message #629078 is a reply to message #629074] |
Wed, 03 December 2014 02:44  |
|
sss111ind
Messages: 634
Registered: April 2012
Location: India
|
Senior Member |
|
|
Thank you very much. I will definitely add that. But for the curiosity I have added already at two places once in analytical, second in query.Is it correct that how many times we will select from an inlive view those many times we need order by clause.And any other way how we can improve the query as well.
[Updated on: Wed, 03 December 2014 02:47] Report message to a moderator |
|
|
|
|
|
| Re: Order by clause [message #629086 is a reply to message #629083] |
Wed, 03 December 2014 03:17 |
|
sss111ind
Messages: 634
Registered: April 2012
Location: India
|
Senior Member |
|
|
Thanks Michel,You mean this
WITH temp AS
(SELECT ROW_NUMBER()OVER(ORDER BY hiredate nulls FIRST) RN,particular,hiredate,sal,comm,
SUM(balance + sal - comm) OVER(ORDER BY hiredate NULLS FIRST) balance
FROM (SELECT 'OPEN' particular,to_date('') hiredate,0 sal,0 comm,344 balance
FROM dual
UNION ALL
SELECT ename,hiredate,NVL(sal, 0) sal,NVL(comm, 0) comm,0 balance
FROM emp2
))
SELECT *
FROM temp
UNION ALL
SELECT MAX(rn) + 1 rn,'TOTAL' particular,to_date('') hiredate,
SUM(BALANCE) sal,SUM(COMM) comm,0 balance
FROM temp
UNION ALL
SELECT MAX(rn) + 2 rn,'FINAL' particular,to_date('') hiredate,
0 sal,0 comm,min (balance) balance
FROM temp
WHERE rn=(select max(rn) from temp)
order by rn;
|
|
|
|
|
|
|
|
| Re: Order by clause [message #629092 is a reply to message #629090] |
Wed, 03 December 2014 03:44 |
|
sss111ind
Messages: 634
Registered: April 2012
Location: India
|
Senior Member |
|
|
The max and min in the final union all we have used for not to use group by again. And the last order by removed and count is added.Hence the query is like
WITH temp AS
(SELECT count(*)OVER(ORDER BY 1) cnt,
ROW_NUMBER()OVER(ORDER BY hiredate nulls FIRST) RN,particular,hiredate,sal,comm,
SUM(balance + sal - comm) OVER(ORDER BY hiredate NULLS FIRST) balance
FROM (SELECT 'OPEN' particular,to_date('') hiredate,0 sal,0 comm,344 balance
FROM dual
UNION ALL
SELECT ename,hiredate,NVL(sal, 0) sal,NVL(comm, 0) comm,0 balance
FROM emp2
))
SELECT RN,particular,hiredate,sal,comm,balance
FROM temp
UNION ALL
SELECT max(rn)+1 rn ,'TOTAL' particular,to_date('') hiredate,
SUM(BALANCE) sal,SUM(COMM) comm,0 balance
FROM temp
UNION ALL
SELECT MAX(rn) + 2 rn,'FINAL' particular,to_date('') hiredate,
0 sal,0 comm,min(balance) balance
FROM temp
WHERE rn=cnt;
|
|
|
|
|
|
| Re: Order by clause [message #629094 is a reply to message #629093] |
Wed, 03 December 2014 03:59 |
|
sss111ind
Messages: 634
Registered: April 2012
Location: India
|
Senior Member |
|
|
WITH temp AS
(SELECT count(*)OVER() cnt,
ROW_NUMBER()OVER(ORDER BY hiredate nulls FIRST) RN,particular,hiredate,sal,comm,
SUM(balance + sal - comm) OVER(ORDER BY hiredate NULLS FIRST) balance
FROM (SELECT 'OPEN' particular,to_date('') hiredate,0 sal,0 comm,344 balance
FROM dual
UNION ALL
SELECT ename,hiredate,NVL(sal, 0) sal,NVL(comm, 0) comm,0 balance
FROM emp2
))
SELECT RN,particular,hiredate,sal,comm,balance
FROM temp
UNION ALL
SELECT cnt+1 rn ,'TOTAL' particular,to_date('') hiredate,
SUM(BALANCE) sal,SUM(COMM) comm,0 balance
FROM temp group by cnt
UNION ALL
SELECT cnt + 2 rn,'FINAL' particular,to_date('') hiredate,
0 sal,0 comm,min(balance) balance
FROM temp
WHERE rn=cnt group by cnt;
[Updated on: Wed, 03 December 2014 03:59] Report message to a moderator |
|
|
|
| Re: Order by clause [message #629095 is a reply to message #629092] |
Wed, 03 December 2014 04:01 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
sss111ind wrote on Wed, 03 December 2014 09:44The max and min in the final union all we have used for not to use group by again.
That makes no sense. You do not need aggregate functions or group by on a select that only ever selects one row.
|
|
|
|
| Re: Order by clause [message #629096 is a reply to message #629092] |
Wed, 03 December 2014 04:02 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
sss111ind wrote on Wed, 03 December 2014 09:44And the last order by removed
You always need order by if you want the results in a particular order.
|
|
|
|
|
|
| Re: Order by clause [message #629100 is a reply to message #629099] |
Wed, 03 December 2014 04:36 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That one needs max.
The final one doesn't need max.
Removing max never causes you to have to add group by - that makes no sense.
|
|
|
|
|
|
| Re: Order by clause [message #629102 is a reply to message #629101] |
Wed, 03 December 2014 04:47 |
|
sss111ind
Messages: 634
Registered: April 2012
Location: India
|
Senior Member |
|
|
It's My mistake I got it finally that already I am using there as means by default I will get 15th row if I add 2 to rn then automatically i will get the seraial number.
sELECT MAX(rn) + 2 rn,'FINAL' particular,to_date('') hiredate,
0 sal,0 comm,min (balance) balance
FROM temp
WHERE rn=(select max(rn) from temp);
Now my query will look like
WITH temp AS
(SELECT ROWNUM RN,particular,hiredate,sal,comm,
COUNT(*) OVER() cnt,
SUM(balance + sal - comm) OVER(ORDER BY hiredate NULLS FIRST) balance
FROM (SELECT 'OPEN' particular,to_date('') hiredate,0 sal,0 comm,344 balance
FROM dual
UNION ALL
SELECT ename,hiredate,NVL(sal, 0) sal,NVL(comm, 0) comm,0 balance
FROM emp2
ORDER BY hiredate NULLS FIRST))
SELECT RN,particular,hiredate,sal,comm,balance
FROM temp
UNION ALL
SELECT MAX(rn) + 1 rn,'TOTAL' particular,to_date('') hiredate,
SUM(sal) sal,SUM(COMM) comm,0 balance
FROM temp
UNION ALL
SELECT rn + 2 rn,'FINAL' particular,to_date('') hiredate,
0 sal,0 comm,balance balance
FROM temp
WHERE rn=cnt
order by rn;
Thank you all.
[Updated on: Wed, 03 December 2014 04:49] Report message to a moderator |
|
|
|
| Re: Order by clause [message #629103 is a reply to message #629102] |
Wed, 03 December 2014 04:51 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
You've already posted code that gets the rn for the final row that doesn't use max at all, so why are you re-adding it?
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 05:43:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
