How do I...sort perpetual calendar

[Shrum]

I have a collection of entries in what I'm using as a perpetual events listing (no year data) in a table with 2 *text* (yes, text) fields structured like this:

-----EVENTS---------
date | event
------|---------------
03.24 | Sean's B-Day
07.04 | Independence Day
12.24 | Christmas
-------------------

What I want to do is display all events with the most current one first and the remainder after. The problem I have starts at the end of the year...the list will only go up to the '1224' entry based on my current SQL statments. The list I want to display should look like:

date | event
-----|---------------
1224 | Christmas
0324 | Sean's B-Day
0704 |

I just don't know the SQL (or term) for this sort of sorting. Can someone tell me what the SQL statement is for dealing with 'text' based fields of this nature and operation?

TIA

=============================
Sean Shrum
Shrum Consulting
C.A.T.S.:

http://cats.shrum.net

Consulting:

http://consulting.shrum.net

Programming:

http://programming.shrum.net

R/C Soaring:

http://soaring.shrum.net

 

[Shrum]

Never mind the sample above as I think most people might be confused by it...Let me change the scenario around.

Let's say I have a list of names that I want to sort starting the list with names that start with 'h'. I want the list to continue thru to 'z' and then continue with the remaining 'a' thru 'g' at the end of the list.

Possible?

TIA

=============================
Sean Shrum
Shrum Consulting
C.A.T.S.:

http://cats.shrum.net

Consulting:

http://consulting.shrum.net

Programming:

http://programming.shrum.net

R/C Soaring:

http://soaring.shrum.net

 

[JarlH]

SELECT * FROM tab
ORDER BY CASE WHEN SUBSTRING(col1 FROM 1 FOR 1) >= 'h' THEN 0
ELSE 1 END CASE,
col1

 

[PruSQLer]

That's pretty good, JarlH

 

[JarlH]

Thank you, PruSQLer.

/Jarl

 

[Shrum]

Hmm...

While I know this isn't the MySQL forum, do you think that statement will work in MySQL? Ultimately, I'll try it tonight when I get home but I'v never used "WHEN" in a SQL statement before and the MySQL website doesn't list it.

...then again I'm sure I haven't used 90% of SQL's capabilities before, for that matter.

Thanx for the info. I'll reply if the string works (or doesn't).

=============================
Sean Shrum
Shrum Consulting
C.A.T.S.:

http://cats.shrum.net

Consulting:

http://consulting.shrum.net

Programming:

http://programming.shrum.net

R/C Soaring:

http://soaring.shrum.net

 

[Shrum]

FYI: WHEN clause is not supported in MySQL (granted, this isn't a MySQL forum but now you know).

However I did find a way to accomplish what I wanted with UNION:

(SELECT * FROM table WHERE `field1`>='h' ORDER BY `field1` ASC) UNION (SELECT * FROM table WHERE `field1`<'h' ORDER BY `field1` ASC);

This gives me 2 recordsets, one one top of the other in the configuration that I want: h-z,a-g

Thanx for the help.


=============================
Sean Shrum
Shrum Consulting
C.A.T.S.:

http://cats.shrum.net

Consulting:

http://consulting.shrum.net

Programming:

http://programming.shrum.net

R/C Soaring:

http://soaring.shrum.net

 

[JarlH]

And if you move the ORDER BY out of the two different SELECTs, your statement will be ANSI SQL compliant (Core SQL-99):

SELECT tab.*, 0 AS order_dummy FROM tab WHERE field1 >= 'h'
UNION
SELECT tab.*, 1 AS order_dummy FROM tab WHERE field1 < 'h'
ORDER BY order_dummy, field1

 

[dnoeth]

Two remarks:
- "CASE WHEN ... END" *is* supported by mysql
- Better use UNION ALL instead of UNION because there are no duplicates, so there's no need to create a distinct answer set.

Dieter