I have a list of products below and I like them to be sorted in a particular way. I was wondering if anybody knew how I can accomplish this... The list is below and how I'd like it to be is after that.

Here's my query...

SELECT DISTINCT partnumber_tbl.partid, partnumber_tbl.partnumber, partnumber_tbl.partdescription, partnumber_tbl.prodid, partnumber_tbl.printerlbldescription, pdppart.prodcat
FROM partnumber_tbl, pdppart
WHERE partnumber_tbl.partnumber = pdppart.pdppartid
ORDER BY partnumber_tbl.partnumber ASC

Of course I'm only showing the partnumber below.

current list retrieved from db.

PSD128200E
PSD128200ER
PSD1282664
PSD1282664S
PSD128266E
PSD128266ER
PSD128266S
PSD1283334
PSD1283334S
PSD128333E
PSD128333ER
PSD1284004
PSD1284004S
PSD128400E
PSD128400ER

The way I'd like it to be sorted

PSD128200E
PSD128200ER
PSD1282664
PSD128266E
PSD128266S
PSD1282664S
PSD128266ER
PSD1283334
PSD128333E
PSD1283334S
PSD128333ER
PSD1284004
PSD128400E
PSD1284004S
PSD128400ER

The order needs to start with the length of the particular partnumber and then by the part. Make sense?

Thanks for any help.

The order needs to start with the length of the particular partnumber and then by the part. Make sense?

The order you show is not by lenght and then partnumber value. Looks like you really want a sort First Order on the 1st 9 caracters of the pn string, then by the Length of the string, then by the last 2 cahracaters of the string if the length is greator then 9. Here is a query that demonstrates this sort on a one column table. If you have PNs longer than 11 characters then you will have to increase the 2nd condition of the MID function. Hope this helps.


/* Query returns string function results with desired sort */
select distinct `PartNumber`,
Left(`PartNumber`,9) as `PartL10`,
Mid(`PartNumber`,10,2) as `PartR2`,
Length(`PartNumber`) as `LenPart`
from `partnumber_tbl`
order by Left(`PartNumber`, 9) ASC,
Length(`PartNumber`) ASC,
Mid(`PartNumber`,10,2) ASC,
Length(`PartNumber`) ASC;


/* Query to return Only the PartNumber field */
select distinct `PartNumber`
from `partnumber_tbl`
order by Left(`PartNumber`, 9) ASC,
Length(`PartNumber`) ASC,
Mid(`PartNumber`,10,2) ASC,
Length(`PartNumber`) ASC;

.