Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Sorting question

    hi all..just a quick question about sorting a query. let us say that i am running a select statement that involves 3 or 4 tables and one of the columns in one of the tables is a divisions column. based on the records, there is a specific division that i want to appear first in the list. its not alphabetically possible nor is it the keys associated with it allow it to be selected first. how would i go about pulling that particular record from the divisions column to be listed first in my query? thanks alot

    tibor

  2. #2
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Try this:

    Code:
    select t1.Divisions, 
           *
    from Table1 t1, Table2 t2, Table3 t3 ...
    where ...
    order by case when t1.Divisions = 'MyFavouriteDivision' then '0' + t1.Divisions 
                  else '1' + t1.Divisions 
             end

    Regards,
    Kris

  3. #3
    Join Date
    Mar 2006
    Posts
    82
    wow thanks! thats a good little snippet to have but unfortunately i will have to find a way to tell crystal reports to do the same thing because it still sorts alphabetically.

    tibor

  4. #4
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    You can add calculated column in CR or TSQL like following:

    Code:
    select t1.Divisions, 
           SortColumnForCR = case when t1.Divisions = 'MyFavouriteDivision' then '0' + t1.Divisions 
                                  else '1' + t1.Divisions 
                             end,
           *
    from Table1 t1, Table2 t2, Table3 t3 ...
    where ...
    order by case when t1.Divisions = 'MyFavouriteDivision' then '0' + t1.Divisions 
                  else '1' + t1.Divisions 
             end
    Regards,
    Kris

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should have a Divisions table with a SortOrder column.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2006
    Posts
    82
    again, thanks but crystal is still sorting abc

  7. #7
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Use SortColumnForCR as a first (indexed) column of your query and hide it.

    Regards,
    Kris

  8. #8
    Join Date
    Mar 2006
    Posts
    82
    ok...im very newbie to this...how would i do that?

  9. #9
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    I am sorry - last time I used Crystal Reports 2 years ago.
    I do not remember details.
    I can help you with the SQL.


    Regards,
    Kris

  10. #10
    Join Date
    Mar 2006
    Posts
    82
    crystal is a pain, lol.

  11. #11
    Join Date
    Mar 2006
    Posts
    82
    ok...got the sql expression down...so how do i "hide" the '0' and '1' out of the column?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should not be doing what you are doing. You are hard-coding datavalues, and that is universally scorned as a bad programming practice.
    You are concatenating sort-orders with your data, and that is another sucky idea.
    Create a divisions table and add a sort order to it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    You should not be doing what you are doing. You are hard-coding datavalues, and that is universally scorned as a bad programming practice.
    http://weblogs.sqlteam.com/jeffs/arc...2/10/9002.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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