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:


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

SELECT * FROM yourtable ORDER BY field1, field2

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.

Just reverse the fields in the order by clause:

Order by MeatType, Index

or whatever those fields are called in your database.

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

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.

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.

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?

Select * from yourtable
....
Group by field1
order by field2

Group by is usually for aggregate functions. That tries crunching all the records into one record.

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)

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.

you are right, the way you want things sorted, is very odd. I cannot see a way to achieve this result

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. :)