as with my slightly previous response, I can't use consecutive integers...otherwise the "months" table is the obvious solution--I don't and can't know the complete set, I need to allow users to make new "months", and then I need to fix the sort order, but preferably NOT by correcting a bunch of numbers.
just loading the first of my 8 CSV files produces 30 different values for "month", and I know that's not complete... *MY* traditional approach (outside a DB) would be to create a custom sort/comparison function that would use a list, where the list order is the sort order, and the comparison fn would use that list to test values against. (well, depending on the size of the list, I might use a hash-table instead of a List, or Vector) but I do want to try your "alias" thing below, that's the kind of thing I was hoping for here... I also have to consider the maintenance aspect here, and I haven't yet--that might argue in favor of a months table... -- clint On Sep 27, 3:49 pm, Kerry Sainsbury <[email protected]> wrote: > 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.
