Marcus Claesson wrote:
Hi!
I have a problem with getting the order I want on a table after new rows have been inserted. I try to simplify it...:
Perhaps you have simplified too much, then, as I simply do not understand what you want. Comments and questions below.
I want to have a one-to-one relationship between 'name' and 'full'. Every 'name' (or'full') have one or more 'parts'. The higher the 'score' the lower the 'full',
How do you achieve this? Is the full really an ID for name, or does it change according to score?
but for my program I have to keep every 'name' next to each other, with ascending 'part' number.
Hence "ORDER BY name, part" or "ORDER BY full, part". OK.
I don't want to use 'name' as an identifier since they can be long sentences.
Then name should be in a separate table, linked by its id (full?).
In other words, I have this old table: SELECT * from table ORDER BY full,part;
name full part score ---- --- --- ----- a 1 1 900 a 1 2 500 b 2 1 800 c 3 1 700 c 3 2 600 c 3 3 500
OK.
and I insert these rows into the same table: (there's nothing I can do about these 'full' values)
d 1 1 1000 d 1 2 400 e 2 1 900 e 2 2 500 f 3 1 700 g 4 1 600
Wait a minute! You said you have a "one-to-one relationship between 'name' and 'full'", but a full of 1 means name is 'a', not 'd'.
And after some manipulation (that I hope someone can help me with) I want a query that gives this:
d 1 1 1000 d 1 2 400 a 2 1 900 e 3 1 900 b 4 1 800 c 5 1 700 c 5 2 600 c 5 3 500 f 6 1 700 g 7 1 600
You start with 6 rows and add 6 rows. That's 12 rows, but your desired output has only 10 rows. How do you determine which rows to keep and which to exclude?
Looking more closely, I see that this is simply not the data you inserted -- every row starting with the third has the wrong full number. Surely you are not saying that you want a query which returns numbers which don't match the actual values in the column, are you? Are you changing all the full values after the second insert?
rather than
a 1 1 900 a 1 2 500 d 1 1 1000 d 1 2 400 b 2 1 800 e 2 1 900 e 2 2 500 c 3 1 700 c 3 2 600 c 3 3 500 f 3 1 700 g 4 1 600
How did you get this? This output cannot have come from the same query, as it is not ordered by full, part. Using your query, you should get
+------+------+------+-------+ | name | full | part | score | +------+------+------+-------+ | a | 1 | 1 | 900 | | d | 1 | 1 | 1000 | | d | 1 | 2 | 400 | | a | 1 | 2 | 500 | | e | 2 | 1 | 900 | | b | 2 | 1 | 800 | | e | 2 | 2 | 500 | | c | 3 | 1 | 700 | | f | 3 | 1 | 700 | | c | 3 | 2 | 600 | | c | 3 | 3 | 500 | | g | 4 | 1 | 600 | +------+------+------+-------+ 12 rows in set (0.01 sec)
At this point, it appears to me that you want to change full according to the new rows, but full,part is apparently your id! That would be a bad idea, so perhaps I've misunderstood. I'd suggest you back up and tell us what you really want to accomplish, without the simplification. Then maybe someone can suggest an effective solution.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
