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"-8×2"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"-8×2"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 |
>Solution :
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.