Sorting at different record levels

smohd1338 · Post by **smohd1338** » Thu Dec 20, 2012 10:43 am

Hi, currently my output is thsi way:
CLIENTID RECORDTYPE EMPNO GroupPolicynumber

ABC101 A1
ABC101 A2
ABC101 A2
ABC101 A3
ABC101 A4
ABC101 A4
ABC101 A5
ABC101 A5
ABC101 B1 7990778998
ABC101 B1 7990789734
ABC101 I1 7990778998 457893275B
ABC101 I1 7990778998 879765992A
ABC101 I1 7990789734 JHJU898977
ABC101 I2 7990778998 457893275B
ABC101 I2 7990778998 879765992A
ABC101 I2 7990789734 JHJU898977
ABC101 I2 7990789734 JHJU898977

and i need it to be sorted this way, i tried many times in many ways, i could not get, any help, wt conconetion should be used while sorting?

ABC101 A1
ABC101 A2
ABC101 A2
ABC101 A3
ABC101 A4
ABC101 A4
ABC101 A5
ABC101 A5
ABC101 B1 7990778998
ABC101 I1 7990778998 457893275B
ABC101 I2 7990778998 457893275B
ABC101 I1 7990778998 879765992A
ABC101 I2 7990778998 879765992A
ABC101 B1 7990789734
ABC101 I1 7990789734 JHJU898977
ABC101 I2 7990789734 JHJU898977
ABC101 I2 7990789734 JHJU898977

jwiles · Post by **jwiles** » Thu Dec 20, 2012 12:13 pm

When you examine the desired output sequence, look at the way the column values are grouped together. Which ones change value while others stay the same? The ones that change are further down in the sort order than those that stay the same. You can do this two columns at a time. For example: comparing columns A and B--if B's values stay the same while A's change, then B is sorted before A (i.e. sort order is B, A).

Regards,

ray.wurlod · Post by **ray.wurlod** » Thu Dec 20, 2012 4:12 pm

Looks like you need to sort by CLIENTID then by EMPNO then by GROUPPOLICYNO then by RECORDTYPE and specify Nulls Position as First.

jwiles · Post by **jwiles** » Thu Dec 20, 2012 4:30 pm

I believe GroupPolicyNumber will come before RECORDTYPE

ray.wurlod · Post by **ray.wurlod** » Thu Dec 20, 2012 9:22 pm

Indeed. I meant to type that. I've amended my earlier post.