Is it possible to group by a substring pervasive sql?

Question

I am trying to write a report and the rows need to be grouped in a "special" way. The "BIN" field is where parts are stored on a shelf. One shelf is bins GS7A01-GS7E01. The bottom is shelf A and the top is shelf E. But what I need is for the data to be grouped by shelf location (GS7A01 - the 7) and then shelf number. (GS7A01 - the 01). So it should have all records for GS7A01,GS7B01,GS7C01, etc listed first and then the next shelf after all of the '01's have been listed -> GS7A02,GS7B02,GS7C02, etc.

I'm able to get the data grouped with the below query but it is not grouped exactly like I need it. Is it possible to group how I need it to be in the query? Or am I better served making a custom script in our ERP system to group it? (It's based off .Net). - not against it just want to see if I can get it done in a query first.

I tried grouping by a substring (the two numbers at the end in GS7A01) of the BIN but i kept getting an error that I need to group by column BIN. if I added it, it doesn't group them by the substring.

We are using pervasive SQL. it's very similar to MySQL but there are some differences

SELECT vim.BIN, vim.PART,vinvm.DESCRIPTION
FROM   V_ITEM_MASTER vim
LEFT OUTER JOIN V_INVENTORY_MSTR vinvm ON (vim.PART = vinvm.PART) AND (vim.LOCATION = vinvm.LOCATION)
WHERE  vim.LOCATION = 'HN' and vim.BIN like 'GS%'
ORDER BY substring(vim.bin,1,4),substring(vim.bin,5,2), vim.PART

Results:

BIN	Part	Descr
GS7A01	874129	EMERGENCY STOP, GENERATOR, PIL
GS7A01	880.20000.0150	CONDUIT,2" AL LB, LB200A
GS7A02	880.99032.0001	COLD SHRINK,1/0-4/0, 15KV
GS7A03	843044	1/4" OD 3/16" ID NYLON BLK TUB
GS7A03	8520134	HHCS, SS, 1-1/8"-7 X 3.5"LG
GS7A03	8521166	ACME THREADED ROD, 1"-4, 3FT
GS7A03	8571303	TUBE, SS,1/4" OD X .035"WAL,6'
GS7A03	8571362	BUSHING, DOUBLE TAP, 2"M X 1/2
GS7A03	8571906	BUSHING REDUCING,4"-2", 304SS
GS7A03	880.15029.0048	LEVEL SENSOR, FLOAT, 4-20mA
GS7A04	880.99029.0067	TERMINAL PAD KIT,3200A,3P
GS7A05	880.24021.0000	CABLE GLAND,NYLON,1-1/2"
GS7A06	880.20094.0010	CONN,METAL FLEX,3",STR,T&B
GS7A06	880.25069.0002	LIGHT,EXTERIOR,AMZ,HOUSE GEN
GS7B02	8521012	HHCS, SS, 5/8"-11 X 3.5" LG
GS7B02	8521014	HHCS, SS, 5/8"-11 X 3" LG
GS7B02	8521052	WASHER, BEVEL, 3/8, GALV. IRON
GS7B02	8521242	HHCS, SS, Moly, 1"-8x2"LG
GS7B02	8521245	WASHER, SQ, UNI-STRUT, 3/8",ZC
GS7B02	852951	WASHER, BEVEL, GALV. IRON, 5/8
GS7B02	880.99032.0002	COLD SHRINK,#2-3/0, 15KV
GS7B03	8521163	ACME HEX NUT, 1"-4, LH, 2G

Need:

BIN	Part	Descr
GS7A01	874129	EMERGENCY STOP, GENERATOR, PIL
GS7A01	880.20000.0150	CONDUIT,2" AL LB, LB200A
GS7B01	xxxxxx	xxxxx
GS7C01	yyyyyy	yyyyy
GS7A02	880.99032.0001	blah blah blah
GS7B02	8521012	HHCS, SS, 5/8"-11 X 3.5" LG
GS7B02	8521014	HHCS, SS, 5/8"-11 X 3" LG
GS7B02	8521052	WASHER, BEVEL, 3/8, GALV. IRON
GS7B02	8521242	HHCS, SS, Moly, 1"-8x2"LG
GS7B02	8521245	WASHER, SQ, UNI-STRUT, 3/8",ZC
GS7B02	852951	WASHER, BEVEL, GALV. IRON, 5/8
GS7B02	880.99032.0002	COLD SHRINK,#2-3/0, 15KV

Answer 1

You need to order by a substring to order by the numeric suffix first.

ORDER BY RIGHT(vim.BIN, 2), vim.BIN, vim.PART

In MySQL RIGHT() extracts the N rightmost characters of the string. If this doesn't exist in Pervasive there should be an equivalent using SUBSTRING().

I don't think you need GROUP BY at all.