Results 1 to 2 of 2

Thread: Sorting a query

  1. #1
    Join Date
    Oct 2003
    Location
    santa clara
    Posts
    25

    Unanswered: Sorting a query

    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.

  2. #2
    Join Date
    Oct 2003
    Posts
    357

    Thumbs up

    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
    Last edited by Madhivanan; 11-16-04 at 07:36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •