ROW NUMBER Partition by

[robert030975]

HI

I'm in need of some help grouping some data


partNo Qualification SequentialNumber SortOrder Sets
877 Vehicle Equipment 10801 00003 1
877 To construction year 10801 00004 1
877 Block Separation 10801 00005 1
877 To construction year 10801 00006 2
877 Vehicle Equipment 10801 00007 2
877 Vehicle Trim Level 10801 00008 1
877 Vehicle Equipment 10801 00009 3
877 Block Separation 10801 00010 2
877 To construction year 10801 00011 3
877 Vehicle Equipment 10801 00012 4
877 Vehicle Trim Level 10801 00013 2

This is after using

ROW_NUMBER() OVER(PARTITION BY Qualification, SequentialNumber, partNo ORDER BY SortOrder ASC)

What I need is to do is within the qualification can you partition by specifically Block Speration ?


so it would read as follows


partNo Qualification SequentialNumber SortOrder Sets
877 Vehicle Equipment 10801 00003 1
877 To construction year 10801 00004 1
877 Block Separation 10801 00005 1
877 To construction year 10801 00006 2
877 Vehicle Equipment 10801 00007 2
877 Vehicle Trim Level 10801 00008 2
877 Vehicle Equipment 10801 00009 2
877 Block Separation 10801 00010 2
877 To construction year 10801 00011 3
877 Vehicle Equipment 10801 00012 3
877 Vehicle Trim Level 10801 00013 3

unsure if it can be done but any help would be great

Thanks


Robert

 

[djj55]

How does it know the two Vehicle Equipment records go with #2? Or how about the Trim level? There would need to be another value to guide what goes where.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[robert030975]

Hi by SequentialNumber ( I know its not great !)

 

[Qik3Coder]

Sequential Number is the same for all your entries, so it cannot break across and start a new number set.
You need a something to group the sets. SQL does not like doing row over row comparisions.

BTW, your trying to use ROW_Number the exact opposite of the way it's supposed to be used.

Raad this:

http://tek-tips.com/viewthread.cfm?qid=1654675

Lodlaiden

You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.