Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ROW NUMBER Partition by

Status
Not open for further replies.
Oct 17, 2006
227
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

 
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!
 
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:

Lodlaiden

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

Part and Inventory Search

Sponsor

Back
Top