Totals Query: Min And Max of Last 6 Digits of 10-digit Serial Number

[VariableZ]

I have a set of serial numbers of the type XXRX123456, where the 'X's indicate the model type, the 'R' is random, and the numbers are sequential. I've created a totals query that groups by LotNumber, then determines the Max and Min values based on the sequential numbers of each serial number and returns the full serial number of the Max and Min values. IE, if I had the following values for a lot number (which could well be written to the table in this order):
XXAX000001, XXEX000004, XXLX000003, XXBX000005, XXGX000002


I'd want it to return XXAX000001 for the Min and XXBX000005 for the Max.

The SQL I've put in place to do this seems inefficient, as I'm currently pulling the serial numbers apart, putting them back together in the form 123456XXRX, determining the Min and Max values, pulling those values back apart, and putting them back together as XXRX123456 again. Does anyone have any better suggestions that don't involve Dfunctions? I can't use Dfunctions at the moment, for reasons that are long and depressing.

In case it helps, I can create fields in the table to populate with pieces of each serial number, such as SerialNumeric = Right(SerialNumber,6) and/or SerialAlpha = Left(SerialNumber,4).

[June7]

You want the Min and Max for each XX model? Or each XX*X model? Or disregard the XX*X altogether? What should determine the grouping?

[VariableZ]

Grouping is determined by lot number. Each distinct lot number has around 50 units, and the units have a numerical order to the serial numbers - for example lot# CH502 might have serial numbers from DFKN000501-DF4N000550 and lot# DL309 might have HD3G002650-HD0G002700 - but they aren't always assembled in that numerical order. In this example, the min and max for CH502 would be DFKN000501 and DF4N000550 and for lot# DL309 would be HD3G002650 and HD0G002700.

Does that clarify?

[June7]

Maybe:

SELECT LotNum, Max(Right(SerialNumber,6)) AS MaxSN, Min(Right(SerialNumber,6)) AS MinSN FROM tablename GROUP BY LotNum;

If you want to reconnect the SN's back to their prefix, inner join this query to the query that splits the SN parts. If the number part could be duplicated across lots, join on LotNum and SerialNumeric.

[VariableZ]

Do you think it would be more efficient to do an inner join or to do the following?:

SELECT
LotNum,
RIGHT(MAX(RIGHT(SerialNumber, 6) + LEFT(SerialNumber, 4)), 4) + LEFT(MAX(RIGHT(SerialNumber, 6) + LEFT(SerialNumber, 4)), 6) AS MaxSN,
RIGHT(MIN(RIGHT(SerialNumber, 6) + LEFT(SerialNumber, 4)), 4) + LEFT(MIN(RIGHT(SerialNumber, 6) + LEFT(SerialNumber, 4)), 6) AS MinSN
FROM
tablename
GROUP BY
LotNum;

[June7]

Try it, does it get you what you want?

[VariableZ]

I won't be able to try it until next week, but I'm sure it would get me the information I'm after. I was just kind of hoping that somebody could pull a rabbit out of a hat that wouldn't require any significant coding changes. :P

Thanks for the suggestion though. I do appreciate it.