Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL Order By related question

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Joseph K Thomas

New User


Joined: 17 Mar 2016
Posts: 4
Location: India

PostPosted: Fri Mar 18, 2016 12:53 pm    Post subject: SQL Order By related question
Reply with quote

Hi Friends

My requirement is to populate the line number based on PolicyNo & Client Number. For example, My table have below columns:
Code:
PolNo   SeqNo   ClientNo
POL1      1      DB
POL1      2      CC
POL1      3      CC
POL1      4      AB
POL2      1      CC
POL2      2      AB
POL2      3      AB
POL3      1      AB
POL3      2      CC
POL3      3      CC
POL3      4      AB
POL3      5      DB

My expected output is:
Code:
PolNo   SeqNo   ClientNo   LineNo
POL1      1      DB      1
POL1      2      CC      2
POL1      3      CC      2
POL1      4      AB      3
POL2      1      CC      1
POL2      2      AB      2
POL2      3      AB      2
POL3      1      AB      1
POL3      2      CC      2
POL3      3      CC      2
POL3      4      AB      1
POL3      5      DB      3

I used below query:
Code:
SELECT POLNO,SEQNO,CLIENTNO,                                               
       DENSE_RANK() OVER (PARTITION BY POL_NO
 ORDER BY CLIENTNO))                           
FROM TABLE ORDER BY 1,2

Got below output:
Code:
PolNo   SeqNo   ClientNo   LineNo
POL1      1      DB      3
POL1      2      CC      2
POL1      3      CC      2
POL1      4      AB      1
POL2      1      CC      2
POL2      2      AB      1
POL2      3      AB      1
POL3      1      AB      1
POL3      2      CC      2
POL3      3      CC      2
POL3      4      AB      1
POL3      5      DB      3

Could you please help me to populate the correct output using dense_rank or any other way?

Thanks in Advance.

code' d
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Mar 18, 2016 2:24 pm    Post subject: Reply to: Dense_Rank related question
Reply with quote

You've got one order for the ranking, and a different order for the final results.

Please use the code tags. Please also explain your expected and received output. We can't guess at what order you want the results to appear in.
Back to top
View user's profile Send private message
Joseph K Thomas

New User


Joined: 17 Mar 2016
Posts: 4
Location: India

PostPosted: Fri Mar 18, 2016 2:42 pm    Post subject: Dense_Rank related question
Reply with quote

Hi Bill

I need to display the record in the order by PolicyNo & SeqNo. That's why I mentioned order by 1,2.

I want to populate the line number based on Policy Number & Client Number. So I used dense_rank like this "DENSE_RANK() OVER (PARTITION BY POL_NO
ORDER BY CLIENTNO))". Because I give the order by ClientNo, the rank value populated based on clientno.

For above example(POL1), my query output is:
Code:
PolNo      SeqNo      ClientNo      LineNo
POL1         1         DB         3
POL1         2         CC         2
POL1         3         CC         2
POL1         4         AB         1

I need the output is:
Code:
PolNo      SeqNo      ClientNo      LineNo
POL1         1         DB         1
POL1         2         CC         2
POL1         3         CC         2
POL1         4         AB         3


If we can't use dense_rank, let me know in which way I can generated the expected output?

Code'd
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Mar 18, 2016 9:24 pm    Post subject: Reply to: SQL Order By related question
Reply with quote

Just add DESC after CLIENTNO :
Code:
SELECT POLNO,SEQNO,CLIENTNO,
 DENSE_RANK() OVER (PARTITION BY POL_NO
 ORDER BY CLIENTNO [b]DESC[/b]))
 FROM TABLE ORDER BY 1,2
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Mar 18, 2016 9:35 pm    Post subject: Reply to: SQL Order By related question
Reply with quote

Assuming that the CLIENTNO is always in reverse sequence when given the SEQNO.
Back to top
View user's profile Send private message
Joseph K Thomas

New User


Joined: 17 Mar 2016
Posts: 4
Location: India

PostPosted: Sat Mar 19, 2016 10:46 am    Post subject: Dense_Rank related question
Reply with quote

Hi Rahul & Bill

Thanks for your reply.

No. The clientno can be in any sequence and it is not depended on the seqno.

For above example, my expected output is
<code>
Code:
PolNo   SeqNo   ClientNo   LineNo
 POL1      1      DB      1
 POL1      2      CC      2
 POL1      3      CC      2
 POL1      4      AB      3
 POL2      1      CC      1
 POL2      2      AB      2
 POL2      3      AB      2
 POL3      1      AB      1
 POL3      2      CC      2
 POL3      3      CC      2
 POL3      4      AB      1
 POL3      5      DB      3

</code>

For the above query, the POL3 getting correct lineno value, because this policy have the clientno in the ascending order. For POL1 & POL2, the lineno value is wrong, because the clientno is not in the ascending order.
Rahul@ If I use DESC for clientno, POL3 lineno getting wrong values.

Is it possible to generate the expected output using sort function?

Code' d AGAIN
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Sat Mar 19, 2016 4:11 pm    Post subject:
Reply with quote

Joseph, please use the code tags to present data, code, screen 'shots' (cut'n'paste) or anything else requiring a fixed pitch font. You can see how it looks by looking at the posts that have been coded for you. Search the forum for how to do it.
Back to top
View user's profile Send private message
Joseph K Thomas

New User


Joined: 17 Mar 2016
Posts: 4
Location: India

PostPosted: Mon Mar 21, 2016 10:11 am    Post subject: Dense_Rank related question
Reply with quote

I am able to generated the expected output using REXX program. Is there any other better way to generate this output?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Apr 05, 2016 6:50 pm    Post subject:
Reply with quote

Code:
select a.*
     ,dense_rank() over (partition by x.polno order by x.polno, x.min_seqno ) as denseR
from mytable A
     ,(select B.polno, min(B.seqno) as min_seqno, B.clientno
        from mytable B
       group by B.polno, B.clientno
       ) X
where a.polno = x.polno
  and a.clientno= x.clientno       
order by A.polno, A.seqno
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fault Analyzer - listings question. egrove IBM Tools 4 Thu Aug 11, 2016 5:31 pm
No new posts dataset copy question - REPRO or some... atulxp TSO/ISPF 2 Wed Aug 03, 2016 10:56 pm
No new posts DFSort Question krrp DFSORT/ICETOOL 9 Mon Mar 07, 2016 7:11 pm
No new posts JCL sort related Mani453 DFSORT/ICETOOL 13 Sun Dec 20, 2015 1:04 pm
No new posts Query related to inserting an auto po... Gopalakrishnan V DB2 4 Fri Dec 18, 2015 12:09 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us