I'm having this issue on how to sort reports based on part numbers i have part numbers that i return have either a part number as in 1000 or if there have been multiple part number could be 1001-2 mean that there were 2 revised copies of the part number.

What i want to return is either the part nubmer itself 1000 or the highest number of that part number say 1001-2 and filter out the 1001 or 1001-1

Example data:
part numbers - 1000,1001,1001-1,1001-2


What i want to return is the 1000 and the 1001-2 and filter out the 1001 and 1001-1

Any ideas?

Create a new colum for the revisions part numbers. I would also add a last modified date colum as well. Then the sql query will allow use to differentiate the two types of numbers.

Or you could append a -0 to all of the part numbers with no revisions.

Either way you go you need each colum to have the same type of information.


|PartNumber | PartRevision | RevisionDate
-----------------------------------------
|1000 |2 |09/19/2005
-----------------------------------------
|1001 |1 |08/14/2005