Hello,
I have a bunch of tracks. Some tracks start with special characters
like Á or ¿ or ¡ ... I want to be able to sort the tracks. I've tried
a couple of solutions, but the code doesn't look clean and i hate bad
code.

I was looking around, and found that iTunes does it pretty much the
way i want it. Basically, they use 2 fields. the name of the track and
the sortname. I found the solution flexible, simple and smart,
specially since trying to sort spanish characters or other special
characters doesn't always work.

For that, i've created two fields on my database. One field called
'name' and another 'sortname'. You guess it... the field 'name' will
be the field i'll use for output and 'sortname' will be the field i'll
use for sorting the track.

The idea is to use the 'sortname' only when it's necessary to sort the
track. In other words, if there is a 'sortname', use it instead of the
'name'. If not, use the 'name' to sort the track.

This way, i don't need to populate the 'sortname' with a copy of the
'name'. Just for those special tracks.


The MySQL would look similar to this:

SELECT track.name
FROM track
ORDER BY ?????

Is this feasable using a query in MySQL?

Thanks
Marco

On Wed, 10 Mar 2010 13:16:25 -0800 (PST), SM wrote:
Yes.

Hints: COALESCE(), temporary tables, and/or careful selection of the
collation. Let us know if you need details.

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting. [TOFU := text oben,
Q: What is the most annoying thing on usenet? followup unten]

On Mar 10, 5:55*pm, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:
Got it! i've done some research... the collation won't work in my
case.. some characters like ¡ or ¿ won´t sort correctly which is
understandable.
I´ve read about coalesce and did some basic testing and it seems to
work; this: coalesce(track.sortname, track.name) works perfect and
it's so simple. My choice for the moment.

For the temporary tables, i have to read about it. My question is
since coalesce works ok and the code is clean, why temporary tables?
Are they faster and/or have an advantage that i should know?

Thanks... i'll keep researching.

On Wed, 10 Mar 2010 16:30:10 -0800 (PST), SM wrote:
They're temporary, go away when the connection does, and invisible to
other connections. They're a great place to hold a sorted table if you
decide to do further corrections to the sorted table or are planning to
do a fair amount of analysis on the sorted data for some reason. That
is, you can store the results of the coalesce as a single field in them.
The coalesce, while fast, isn't free, and (more importantly to the stuff
I end up doing often) it's still a derived column until you put the
results into something else.

--
The Write Many, Read Never drive. For those people that don't know
their system has a /dev/null already.
-- Rik Steenwinkel, singing the praises of 8mm Exabytes

On 11 mar, 14:30, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:
[...]
Are you assuming that the table is sorted because you inserted rows in
a particular order?

/Lennart

[...]

On Thu, 11 Mar 2010 13:37:08 -0800 (PST), Lennart wrote:
What? You think I'm new or somthing? (;

No, but the important thing, which you clipped off so your point would
be important, is that one isn't looking in two places to determine
order, which means it's POSSIBLE at all to build an index over the table
on that column. Can't build indexes on derived columns, can't use 'em
for WHERE clauses with an index, etc.

--
Every normal man must be tempted at times to spit upon his hands, hoist
the black flag, and begin slitting throats.
-- HL Mencken

On 11 mar, 23:35, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:
[...]
Looking at you're usenet history, no

You claimed (at least I interpreted it that way) a common
misunderstanding, which is why I asked you to clarify, nothing more,
nothing less. The important thing is that someone might read your
statement about sorted tables, and end up expecting that the table can
be treated as such.


/Lennart

[...]

On Thu, 11 Mar 2010 21:31:57 -0800 (PST), Lennart wrote:
Ah, so you're thinking by "sorted table" I meant "one wot I wrote rows
to in order" rather than "one wot I'm accessing via an index". The
latter's the case. (Although, thinking about it, a simple temporary
table on a non=cluster table, being bound to a single connection, I'd
actually take pretty good (but not never) odds that it would read rows
back out in the order they went in.)

--
75. I will instruct my Legions of Terror to attack the hero en masse,
instead of standing around waiting while members break off and
attack one or two at a time.
--Peter Anspach's list of things to do as an Evil Overlord

On 12 mar, 17:39, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:
That is certainly true, but assuming that it is so, is begging for
trouble. Within the same version of the dbms it may work 99.999...% of
the time, the remaining ones will probably cause some confusion.
Furthermore, will it work the next time you upgrade your dbms to a
never version?

Anyhow, I think we are in agreement, so a continued discussion would
probably end up rather boring :-)

/Lennart