SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict Shalin's Avatar
    Join Date
    Sep 2003
    Location
    __beyond
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problems in Order By clause

    Hi,

    I have problem in sorting records. I'm using SQL2000

    I have written a select statement from which I am getting the Desc

    Select PageDesc from PageMaster where PageId in (100,102,105,101)

    where 100- z
    102 - a
    105 - c
    101- b

    But this query returns me

    a
    b
    c
    z

    (SQL by default orders it alphabetically).

    where as I want the result in
    z
    a
    c
    b

    (which is in the order of my page sequece:100,102,105,101)

    Need Advice

    Cheers,
    Shalin
    Help Desk Software - Helpdesk Pilot
    Flash Screensaver - Screenswift for Windows
    Online Polls - Tezaa.com
    Address Book Software - Addza for Windows

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...order by pageid ?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, it looks like you have a special sequence

    the only way to sort by this special sequence is if you give the database the sequence values, e.g.
    Code:
    Select PageDesc 
      from PageMaster 
     where PageId in (100,102,105,101)
    order
        by case PageId 
           when 100 then 1
           when 102 then 2
           when 105 then 3
           when 101 then 4
           end
    naturally, this sucks if the PageIDs that you run this on vary from execution to execution

    in that case, i would store the sort sequence value in the pageMaster table

    rudy
    http://r937.com/

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    ...
    in that case, i would store the sort sequence value in the pageMaster table
    ...unless the sequence is created by the user (order in which checkboxes have been checked?)


    Given:
    Sequence of IDs, "100,102,105,101"
    To do:
    Split the string into an array
    Loop through the values in the array to build a new string like
    "when 100 then 1
    when 102 then 2 ...etc"

    Can be done with sql, but I think script is easier

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    " ... order in which checkboxes have been checked?"

    yeah, but you have to use javascript to actually capture that order, before the form is submitted, because checkboxes always arrive in source html order

    maybe something like this: http://r937.com/sortdropdown.html

    this is the type of technique that allows users to customize a sort sequence, and like you suggest, jofa, it typically requires scripting in order to create the customized sql query that is then executed dynamically

    the only time you'd have a stored sort sequence is when it doesn't change from run to run but still requires custom sorting

    for example, sort USA and Canada first and second, then other countries alphabetically

    rudy

  6. #6
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    ... checkboxes always arrive in source html order ...
    Yep, I assume the string "100,102,105,101" would be the value of a hidden element, not the list of checkbox values directly

    (If it is the list of checkbox values, then you have the sort order stored somewhere, because that's how you decided in which order to print out the elements in the form - correct?)

  7. #7
    SitePoint Addict Shalin's Avatar
    Join Date
    Sep 2003
    Location
    __beyond
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks all.

    r937.. something like that would help if the no. of values were less.. here i'm expecting something around 20-30 no.s in the sequence and many such sequence. its not being generated by an HTML page.

    I'm working further on this problem statment.
    Thanks for all the help.

    Cheers,
    Shalin
    Help Desk Software - Helpdesk Pilot
    Flash Screensaver - Screenswift for Windows
    Online Polls - Tezaa.com
    Address Book Software - Addza for Windows

  8. #8
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, how about inserting the sequence of numbers into a temp table (one auto_increment column, one column for the numbers), and then join with PageMaster and order by that auto_increment column?

  9. #9
    SitePoint Addict Shalin's Avatar
    Join Date
    Sep 2003
    Location
    __beyond
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay. here is something which i have tried:

    declare @tbl table (PageId int, srt int)

    insert @tbl

    select 100, 1

    union all

    select 102, 2

    union all

    select 105, 3

    union all

    select 101, 4





    then

    select pm.PageDesc

    from PageMaster pm

    join @tbl tmp

    on tmp.PageId = pm.PageId

    order by tmp.srt


    it looks fine. but i'm not sure how to seperate the comma seperate fields to apply to this.

    any ideas.

    Cheers,
    shalin
    Help Desk Software - Helpdesk Pilot
    Flash Screensaver - Screenswift for Windows
    Online Polls - Tezaa.com
    Address Book Software - Addza for Windows


Bookmarks

Posting Permissions

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