Home » SQL & PL/SQL » SQL & PL/SQL » Help for SQL (10g)
| Help for SQL [message #569119] |
Sat, 20 October 2012 15:02  |
 |
cool99181
Messages: 3
Registered: June 2011
Location: Pune
|
Junior Member |
|
|
Hi,
I have the following query. The problem is that in case of dense_rank it gives wrong result when there is multi sort involved,
So lets say if following data i get from inner query before applying dense_rank
Seq_uid Status Salary Pre_sort_col
1 A 4 A
1 A 3 A
2 B 5 B
3 A 0 A
After dense_rank when ordering by pre_sort_col desc and seq_uid desc the result set is
Seq_uid Status Salary ROW_NUM pre_sort_col
3 A 0 1 A
1 A 4 3 A
1 A 3 3 A
2 B 5 2 B
which is wrong as seq_uid 3 shld nt come first. I cant have salary in dense rank as these are dynamic columns,if on 3 columns multi sort is selected,the first column will come in pre_sort_col, SEq_uid has to be used as to distinguish from other records which get same pre_sort_col
SELECT c.ROW_NUM, c.RECORD_TOTAL, VW.*
FROM (SELECT distinct seq_uid,ROW_NUM,RECORD_TOTAL
FROM (SELECT dense_rank(order by pre_sort_col desc,seq_uid desc)row_num,a.*
FROM (SELECT COUNT(DISTINCT V1.seq_uid) OVER() RECORD_TOTAL,
MAX(deal_status) OVER(PARTITION BY seq_uid ORDER BY seq_uid desc) PRE_SORT_COL,
seq_uid,
status,
salary
FROM DC_VW V1
WHERE UPPER(STATUS_ID) IN
(500,501)
AND (RON_ID IN (36,37,38,39) OR
CNTRY_ID IN (0) OR
)
ORDER BY PRE_SORT_COL deSC,
STATUS DESC,
salary desc
) A
ORDER BY PRE_SORT_COL DESC,
status desc,
salasr desc
) B
WHERE ROW_NUM between 1 AND 30 ) C,
DC_VW VW
WHERE c.seq_uid = VW.seq_uid
ORDER BY STATUS DESC,
salary desc,
vw.seq_uid desc
*BlackSwan added {code} tags. Do so yourself in the future!
[Updated on: Sun, 21 October 2012 00:23] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Help for SQL [message #569124 is a reply to message #569119] |
Sat, 20 October 2012 17:42  |
Solomon Yakobson
Messages: 1442
Registered: January 2010
|
Senior Member |
|
|
cool99181 wrote on Sat, 20 October 2012 16:02Hi,
I have the following query. The problem is that in case of dense_rank it gives wrong result when there is multi sort involved,
So lets say if following data i get from inner query before applying dense_rank
Seq_uid Status Salary Pre_sort_col
1 A 4 A
1 A 3 A
2 B 5 B
3 A 0 A
After dense_rank when ordering by pre_sort_col desc and seq_uid desc the result set is
Seq_uid Status Salary ROW_NUM pre_sort_col
3 A 0 1 A
1 A 4 3 A
1 A 3 3 A
2 B 5 2 B
which is wrong as seq_uid 3 shld nt come first.
Your code has syntax errors. But in any case, I can't reproduce it:
SQL> with t as (
2 select 1 Seq_uid,'A' Status,4 Salary,'A' Pre_sort_col from dual union all
3 select 1,'A',3,'A' from dual union all
4 select 2,'B',5,'B' from dual union all
5 select 3,'A',0,'A' from dual
6 )
7 select t.*,
8 dense_rank() over(order by pre_sort_col desc,seq_uid desc)
9 from t
10 /
SEQ_UID S SALARY P DENSE_RANK()OVER(ORDERBYPRE_SORT_COLDESC,SEQ_UIDDESC)
---------- - ---------- - -----------------------------------------------------
2 B 5 B 1
3 A 0 A 2
1 A 4 A 3
1 A 3 A 3
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Tue Jun 18 23:15:42 CDT 2013
Total time taken to generate the page: 0.12055 seconds
|
] 
Members
Search
Help
Register
Login
Home
