Forum OpenACS Q&A: Postgres smart sort order for book titles...

Collapse
1: Postgres smart sort order for book titles...
Posted by Patrick Giagnocavo on 06/08/01 05:16 PM
I have a database of titles.  I want to do a "smart" ordering of
titles, where common words like "the" and "A" are ignored for
purposes of the sort.  For instance, a book called "A History of
the English Speaking Peoples" should sort with the H's rather
than appear at the top, with the A's.

I understand the CASE WHEN construct, but I don't want to
modify the title, just the sort order.  My understanding is that the
SORT BY part is done in a separate step from table retrieval, so I
can't really mess with the SELECT to get what I want.

Any ideas on the best way to do this?

Collapse
2: Response to Postgres smart sort order for book titles... (response to 1)
Posted by Jonathan Ellis on 06/08/01 06:13 PM
I'm not sure I understand the question.  Why can't you "mess with the select"?  Instead of "order by title" say "order by title_words(title)" and define a function title_words that strips out "the", "a", "is", etc. and only leaves the "important" stuff.  You can create an index on this function to further speed things if you need to.
Collapse
3: Response to Postgres smart sort order for book titles... (response to 1)
Posted by Marc Spitzer on 06/08/01 08:17 PM
you could add a colum to your table that holds all the words from the title that you want to sort on, use an insert triger and a stored procedure to create it from the title then sort on that but display the real title.

Good luck

marc

Collapse
4: Response to Postgres smart sort order for book titles... (response to 1)
Posted by Jonathan Ellis on 07/09/01 11:22 PM
I had to do this for my own site so here is my code: 

create function title_sort_string(varchar) returns varchar as '
    set word_list [split $1 { }]
    if { [lsearch -exact [list a an the] [string tolower [lindex $word_list 0]]] >= 0 } {
       set word_list [lreplace $word_list 0 0]
    }
    return [join $word_list { }]
' language 'pltcl';
then you can do select * from books order by title_sort_string(title)

Of course this is pretty simplistic, but it suits my needs. If you want to see some real rules for sorting publication titles, see this.