Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts

    Help with order by clause

    I'm trying to order something by two fields. I really want to "order" by field2 but I need to "group" everything in field1 first.

    Suppose these are my records:

    Code:
    1   ham     other stuff
    5   turkey  some description
    8   salami  blah
    1   spam    test data
    I want to group all of the 1's together (as below), but I want to order by the type of meat.

    Code:
    1   ham     other stuff
    1   spam    test data
    8   salami  blah
    5   turkey  some description
    Last edited by arnyinc; 01-11-2006 at 09:59 PM.

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    SELECT * FROM yourtable ORDER BY field1, field2
    I am the luckiest man in the world

  • #3
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    That only gets me halfway there. If I use your solution I get:

    1 ham other stuff
    1 spam test data
    5 turkey some description
    8 salami blah

    I really want to have:

    1 ham other stuff
    1 spam test data
    8 salami blah
    5 turkey some description

    I want salami to come before turkey in an alphabetic sort.

    Basically, I want to group all the numbers in field1 together and then sort on field2 while still keeping all the numbers grouped together.

  • #4
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,073
    Thanks
    2
    Thanked 22 Times in 22 Posts
    Just reverse the fields in the order by clause:

    Order by MeatType, Index

    or whatever those fields are called in your database.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #5
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    But then it sorts by meattype before index and the numbers are not grouped together. I would get:

    1 ham other stuff
    8 salami blah
    1 spam test data
    5 turkey some description

    Instead of:

    1 ham other stuff
    1 spam test data
    8 salami blah
    5 turkey some description

  • #6
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,073
    Thanks
    2
    Thanked 22 Times in 22 Posts
    You won't be able to use the "order by" clause to get what you want. "Order by" will take field1 and order by that, then take everything within field1 and order it by field2. That's why you're getting results one way if you do "order by field1, field2" vs. "order by field2, field1."

    You'll have to either change your data so it will sort the way you want, or write a special routine to do it. Maybe there's something you could do with loading it up into an array first - I don't know.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #7
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Yes that's true. That's what I was trying to express when I said I needed the records "grouped" by one field and "ordered" by the other. I was hesitant to use group since that has a totally different meaning in Oracle.

    Aaaanyways, I just wanted to make sure there was no easy answer to my question or something simple I was missing.

  • #8
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,073
    Thanks
    2
    Thanked 22 Times in 22 Posts
    Actually, I thought about this a little more and was wondering something. Instead of having a select by:

    1 ham other stuff
    1 spam test data
    8 salami blah
    5 turkey some description

    etc.

    Couldn't you change that first item to a description?

    pork ham other stuff
    pork spam test data
    stuff salami blah
    poultry turkey some description

    Then do your "order by" as:

    order by description, meat

    You'd have your data sorted the way you wanted it. Do you have something like that in your database that you could use for the sort?
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #9
    New to the CF scene
    Join Date
    Oct 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Select * from yourtable
    ....
    Group by field1
    order by field2

  • #10
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Group by is usually for aggregate functions. That tries crunching all the records into one record.

  • #11
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    Actually, the usage of ORDER BY with 2 fields to use for ordering should do the trick.
    explanation
    I am the luckiest man in the world

  • #12
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by Roelf
    Actually, the usage of ORDER BY with 2 fields to use for ordering should do the trick.
    explanation
    Nope, unfortunately it doesn't. Looking the tables I've typed out above, no combination of "order by" gives me the order that I want.

  • #13
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    you are right, the way you want things sorted, is very odd. I cannot see a way to achieve this result
    I am the luckiest man in the world

  • #14
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Thank you for your time Roelf and everyone else. I've been working on a custom solution in my free time, but I just wanted to make sure I wasn't missing something obvious.

    Like you said, it is a very odd sort. Usually when something like this happens it means I've done something wrong with the database design but I'm just playing with the cards that were dealt to me.


  •  

    Posting Permissions

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