PDA

View Full Version : Help with order by clause



arnyinc
Jan 11th, 2006, 09:02 PM
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:



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.



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

Roelf
Jan 12th, 2006, 06:18 AM
SELECT * FROM yourtable ORDER BY field1, field2

arnyinc
Jan 12th, 2006, 03:33 PM
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.

vinyl-junkie
Jan 12th, 2006, 04:03 PM
Just reverse the fields in the order by clause:

Order by MeatType, Index

or whatever those fields are called in your database.

arnyinc
Jan 13th, 2006, 02:18 PM
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

vinyl-junkie
Jan 13th, 2006, 02:38 PM
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.

arnyinc
Jan 13th, 2006, 02:45 PM
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.

vinyl-junkie
Jan 13th, 2006, 06:36 PM
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?

princessdb
Jan 17th, 2006, 03:36 PM
Select * from yourtable
....
Group by field1
order by field2

arnyinc
Jan 17th, 2006, 08:58 PM
Group by is usually for aggregate functions. That tries crunching all the records into one record.

Roelf
Jan 18th, 2006, 06:32 AM
Actually, the usage of ORDER BY with 2 fields to use for ordering should do the trick.
explanation (http://www.w3schools.com/sql/sql_orderby.asp)

arnyinc
Jan 19th, 2006, 02:20 PM
Actually, the usage of ORDER BY with 2 fields to use for ordering should do the trick.
explanation (http://www.w3schools.com/sql/sql_orderby.asp)

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.

Roelf
Jan 20th, 2006, 09:28 AM
you are right, the way you want things sorted, is very odd. I cannot see a way to achieve this result

arnyinc
Jan 26th, 2006, 03:55 PM
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. :)