|
View previous topic :: View next topic
|
| Author |
Message |
Joseph K Thomas
New User
Joined: 17 Mar 2016
Posts: 4
Location: India
|
|
|
|
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 |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Joseph K Thomas
New User
Joined: 17 Mar 2016
Posts: 4
Location: India
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Assuming that the CLIENTNO is always in reverse sequence when given the SEQNO. |
|
| Back to top |
|
|
Joseph K Thomas
New User
Joined: 17 Mar 2016
Posts: 4
Location: India
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| 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 |
|
|
Joseph K Thomas
New User
Joined: 17 Mar 2016
Posts: 4
Location: India
|
|
|
|
| I am able to generated the expected output using REXX program. Is there any other better way to generate this output? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
|
|
