MySQL ORDER BY dictionary query.

Ok, I try to answer this.

Firstly, I think the basic problem here is, that you want to have business logic on the database or at least the data layer. Not a good design approach! You should get the data back as it is on the database. This means it can be sorted by column in desc or asc order. All other logic should happen in the business layer.

Secondly, your table structure seems to be in the need to get normalized! You should have a table called language with i.e. languageId and languageName. This should be linked with your text table. You could then introduce a ranking field in the language table and sort by the ranking field. If you have a lot of repeating values in a database table column it is most of the time a sign that you need a seperate table for it!

The quick and dirty way: Create a temporary table, fill the table with your result data, add a ranking column, fill data into the ranking column using:
CASE
WHEN language = 'spanish' THEN 0
WHEN language = 'english' THEN 1
WHEN language = 'german' THEN 2
END

The output is then
SELECT * FROM #tempTable
ORDER BY rank

Just my 2 cents!

Cheers

Peter

Hi Peter!

I was pleased to get your response on this, though a bit surprised about the analysis of my business logic - and I didn't even know I had a business :-)

I won't go into the big concept of my model here, but I do want to try to sell the point again that all I want to do is to get the data as it is and sort by a data column (as you write) - the only difference being that I want to alter the comparison algorithm between values. As the alphabetic sequence of letters from A to Z is just a coincidence, I do not see a problem in altering this on this level. In any case, the best reason for doing this in the database layer would be to gain performance (and I can't guarantee that it will actually do so).

The other thing about normalising, I see your point, but I like to keep my number of tables and joins low when possible. Anyway it's a simple case to filter language out into another table if that should become necessary at some stage.

I got an answer for my question, thereby learned something about the CASE construct!

The following query does what I intended:

SELECT *,
(CASE
WHEN language = 'spanish' THEN 0
WHEN language = 'english' THEN 1
WHEN language = 'german' THEN 2
END) as rank
FROM text ORDER BY rank

Jakob

Hi Jakob,

well, you will always have business logic! Your business logic is maybe: "Show the dictonary entries on a screen ranked by language". Please note, I do not use the word "ordered" because this is different.

The data layer should always only return the data in the best possible way. If the data isn't supporting the view, you will have to handle this one layer up (however you want to call this layer!)

By the way, you talk about performance. The CASE construct is one of the worst if it comes to performance (my experience) having the data in memory in a C# app and then doing the ranking would be a faster way! Also keeping the number of joins low and having tables with redundant data isn't a well perfoming design! A database should be normalized as much as it makes sense! This is a basic design paradigm. The question if it is a "simple case to filter language out into another table if that should become necessary at some stage." is in most cases not correct! It is the opposite. The most difficult task in a project is always if you have to restructure your data design furtehr down the track. It is the most important thing to get your data model sorted out BEFORE you start designing and developing your product!

If you look at the recent development in database programming and you look at technologies such as LINQ from Microsoft, you will find that what you try to achive on the database will happen in the code and not on the database. The database only provides the data model and the relational information. Everything else happens one layer up!

If you got an answer to your problem, why don't you then set this question to "Answered"?

I am sorry that you were obviously offended by my comments. I just wanted to help. But this was obviously not achived by my answer. I will be more cautious next time when I have to decide if I want to answer a question or not!

Cheers

Peter

Dear Peter!
I am sorry if it sounded like I didn't appreciate your help - because I do! I was actually amused by the turn of the discussion, but probably it should have taken place under a different question.
I didn't close the question before because I like to give everybody a chance to respond to my post before I do so.
Thanks again, I'm sure you know what you are talking about, and if it the CASE statement is really that slow, I'll look for another solution.
Jakob

hi sir
i have a table name(cc.call).there are several attributes .some importent are 'starttime', 'sessiontime''calldestination','destination','session bill'.

this table have the record of call that people talk in world..we wish to that i enter date(starttime) and for all country(destnation)'s total bill will display.so if u know then sendf the approprites query .thank u