Home » SQL & PL/SQL » SQL & PL/SQL » Help for SQL (10g)
Help for SQL [message #569119] Sat, 20 October 2012 15:02 Go to next message
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 #569120 is a reply to message #569119] Sat, 20 October 2012 15:19 Go to previous messageGo to next message
BlackSwan
Messages: 22488
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Help for SQL [message #569124 is a reply to message #569119] Sat, 20 October 2012 17:42 Go to previous message
Solomon Yakobson
Messages: 1950
Registered: January 2010
Senior Member
cool99181 wrote on Sat, 20 October 2012 16:02
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. 



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.
Previous Topic: Trying to get employee hire annyversary
Next Topic: tz_offset shows different value for different database
Goto Forum:
  


Current Time: Wed Jul 23 17:41:27 CDT 2014

Total time taken to generate the page: 0.16050 seconds