Sorting a query

pokermagic · #1 (**permalink**) 11-15-04, 17:05

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.

Madhivanan · #2 (**permalink**) 11-16-04, 06:08

Hi, try this

Code:

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
group by substring(partnumber_tbl.partnumber ,4,6),len(substring
(partnumber_tbl.partnumber ,4,len
(partnumber_tbl.partnumber ))),partnumber_tbl.partnumber 
partnumber_tbl.partid, partnumber_tbl.partnumber, 
partnumber_tbl.partdescription, partnumber_tbl.prodid, 
partnumber_tbl.printerlbldescription, pdppart.prodcat

I created a table called part and inserted the values you have given and tested. It has given the required result. I used the following for the table I created

Code:

select partname  from part 
group by substring(partname,4,6),len(substring(partname,4,
len(partname))),partname

which produced the result that you wanted

Madhivanan

Internet Services

Web Development

Digital Marketing

Consumer Tech