I have a table 'TEST' with two columns:

theCharacter CHAR(1)
theNumber NUMBER(1)

i have the following records in the table:

'B', 1
'B', 2
'B', 3
'B', 4
'A', 5
'A', 6
'A', 7
'B', 8
'B', 9

How do I build my query to get the following resultorder:

'B', 1
'B', 2
'B', 3
'B', 4
'B', 8
'B', 9
'A', 5
'A', 6
'A', 7

so that the result is grouped by theCharacter and ordered by theNumber?

select character, number from test order by character desc, number asc

Thats not quite it. because if I would have the following recordsets:

"A", 1
"A", 2
"A", 3
"B", 4
"B", 5
"B", 6
"A", 7
"A", 8
"A", 9

I would like to get the result in the follwing order:

"A", 1
"A", 2
"A", 3
"A", 7
"A", 8
"A", 9
"B", 4
"B", 5
"B", 6

IKA, your sollution would return the following:

"B", 4
"B", 5
"B", 6
"A", 1
"A", 2
"A", 3
"A", 7
"A", 8
"A", 9

If you have following records in the table TEST:

"A", 1
"A", 2
"A", 3
"B", 4
"B", 5
"B", 6
"A", 7
"A", 8
"A", 9

then "select * from test order by charact asc, nr asc" must return following:

"A", 1
"A", 2
"A", 3
"A", 7
"A", 8
"A", 9
"B", 4
"B", 5
"B", 6

Schnaaf, that's not fair, in your first post you specifically said you wanted the Bs ahead of the As, and that requires desc for theCharacter in the ORDER BY

In my first post I specifically asked for a sollution so that the result is grouped by theCharacter and ordered by theNumber.

I need a query sollution that will fit for all data. The given data was just an example, nothing more.

As I said before:

"so that the result is grouped by theCharacter and ordered by theNumber?"

Maybe I didnt express myself right.

What I want is all characters grouped, thus all A's together and all B's together and all C's together, ect (but not nescissarily in that order). The order should be determined by the first number that belongs to every letter.

Look at theNumber column as if it is an auto_increment column. But with every insert the theCharachter column can cantain a different character.


So, to make my dataset adequate, this is my testdata:

"A", 1
"A", 2
"C", 3
"B", 4
"B", 5
"B", 6
"A", 7
"C", 8
"B", 9

And I need the following result:
"A", 1
"A", 2
"A", 7
"C", 3
"C", 8
"B", 4
"B", 5
"B", 6
"B", 9

Note that my needs haven't changed in all three examples, but maybe it wasnt exclusive enough. Hope somebody has an idea.

"The order should be determined by the first number that belongs to every letter"

wow, do you realize how much more difficult this will make the solution

are you sure this is what you want?

because the query will be extremely complex

I know,

That's why I ask the question here.

I have been a database programmer for 5 years on Oracle and on MySQL for 1 year now and I don't have a clue on how to handle this.

The sollution smells like subselects, but even with subselects I wouldnt know how to solve it.

For convenience I will exlplain why I need this kind of sorting/grouping.

I am working on a stats module for our CMS.

look at the theCharacter as a (PHP)sessionID value
look at the theNumber as a timestamp value

I want an overview of the timestamps (a request) grouped together per sessionID while maintaining the original order of the sessioncreation.

"SESSION_ID_1", "20040402120000"
"SESSION_ID_1", "20040402120100"
"SESSION_ID_2", "20040402120200"
"SESSION_ID_3", "20040402120300"
"SESSION_ID_2", "20040402120400"
"SESSION_ID_2", "20040402120500"
"SESSION_ID_1", "20040402120600"
"SESSION_ID_3", "20040402120700"
"SESSION_ID_2", "20040402120800"
"SESSION_ID_3", "20040402120900"

should result in:

"SESSION_ID_1", "20040402120000"
"SESSION_ID_1", "20040402120100"
"SESSION_ID_1", "20040402120600"
"SESSION_ID_2", "20040402120200"
"SESSION_ID_2", "20040402120400"
"SESSION_ID_2", "20040402120500"
"SESSION_ID_2", "20040402120800"
"SESSION_ID_3", "20040402120700"
"SESSION_ID_3", "20040402120300"
"SESSION_ID_3", "20040402120900"

this way I can retrieve the order in which the sessions have been created in time plus I can learn about the clickingorder in time per session ID

even with subselects you have a huge problem

here's a suggested approach:

create a temp table with an auto_number, sessionID, and timestamp

insert into temptable
select sessionID, MIN(timestamp) as firstrow
from yourtable
group by sessionID
order by firstrow

now join your main table back to this temp table, and use the auto_increment value as the ORDER BY