Assuming your table is called "some_data", and has a column called "month",
you could do one
of the following:
Option 1: (The traditional solution -- Join to a "months" table)
create table months(month varchar(15), sortvalue int);
insert into months(month , sortvalue) ('January', 1);
insert into months(month , sortvalue) ('February', 2);
insert into months(month , sortvalue) ('March', 3);
...
select * from some_data
join months on months.month = some_data.month
order by months.sortvalue
Option 2: (The sexier solution -- Use an Alias)
create alias MonthSort as
$$
int monthSort(String m) {
if (m.equals("January")) return 1;
if (m.equals("February")) return 2;
if (m.equals("March")) return 3;
...
return 0;
}
$$
create table some_data(month varchar(15));
insert into some_data(month) values ('January');
insert into some_data(month) values ('February');
insert into some_data(month) values ('March');
select * from some_data order by monthSort(month)
I'm not sure which would be faster, but they'll both work.
Cheers
Kerry
On Mon, Sep 27, 2010 at 6:40 PM, Rami Ojares <[email protected]> wrote:
> One way would be to use integers to represent the months
> 1 = Jan, 2 = Feb ...
>
> - Rami
>
>
> On 27.9.2010 3:06, Clint Hyde wrote:
>
>> I have a table that is about months...names to be precise...which of
>> course have
>>
>> ascii_name_order != date_time_order
>>
>>
>> that said, all my incoming info is about the month name.
>>
>> so I'd like to create a custom sort/collation order for this one column in
>> this one table.
>>
>> How do I do that?
>>
>> -- clint
>>
>> ------
>>
>> my db is close to going live for public consumption...I have ~ 1 million
>> records in it, text indexing is working properly (yay! thanks!)
>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected]<h2-database%[email protected]>
> .
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
