0

Hi I would like to ask if there is a smart way to change the record position of a table in mysql. My problem is this: I have a table with statistics data and when I recover all the details in a table called "details" I get the following result:

id | name
 1 | Residents
 2 | In age from 0 to 6  
 3 | In age from 6 to 14 
 4 | In age from 14 to 30 
 5 | In age from 30 to 66  
 6 | Total school population
 7 | Students in kindergarten
 8 | Students in high school
 9 | ... 
10 | ...
11 | 66 years and over 

I would like the 11th record to be right after the 5th, thus right after "In age from 30 to 66". Is there a smart way to do that?

Thanks!

0

A database is NOT Excel. The position of a column in a table is - in regard to normal USE of the table - completely arbitrary.

To receive results from a table, you query it - and in that query, you can modify the (selected) column order to get your desired results.

In the same vein, storing computed values (your columns 2-5, 11) primarily makes sense in a time-critical context (i.e. when storage (space and speed) is no issue, but data retrieval has to be extremely quick). I'm not really seeing that in your scenario.

It would be better to simply store the age and to compute which category it belongs to in your select query.

  • As an addendum: I'm not sure why you split the agegroups into columns. If I had to implement this, i would use an age column in my residents table. I would then use a second table to define my age groups (columns: AG_ID, AG_min, AG_max). By joining your tables you could then directly return the age group for each member instead of having to check which column is marked. (Also please be careful with min/max ages. It usually makes more sense to split 0-6 and 7-14, instead of 6-14) – Sascha Rambeaud Mar 15 '14 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.