Announcement

Announcement Module
Collapse
No announcement yet.

Strange Sorting/Updating Problem

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Strange Sorting/Updating Problem

    Hello!

    I'm using a mix of UPDATE and SELECT to pre-sort rows for a faster SELECT later on. This is my query:


    SET @num = 0;UPDATE rel_artist_album LEFT JOIN ( SELECT raa.album_id, CASE WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.album_fullength = 1 AND a.is_movie = 0 AND a.is_live = 0 AND a.is_compilation = 0 AND a.is_misc = 0) THEN 1 WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.album_fullength = 0 AND a.is_movie = 0 AND a.is_live = 0 AND a.is_compilation = 0 AND a.is_misc = 0) THEN 2 WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_movie = 1) THEN 3 WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_live = 1) THEN 4 WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_compilation = 1) THEN 5 WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_misc = 1) THEN 6 WHEN (a.is_bootleg = 1) THEN 7 WHEN (a.album_fullength = 1 AND a.is_sampler = 1 AND a.is_bootleg = 0) THEN 8 END AS sort_col FROM rel_artist_album AS raa LEFT JOIN albums AS a ON raa.album_id = a.album_id WHERE raa.artist_id = 541 ORDER BY sort_col ASC, IF (a.is_sampler = 1, a.album_name, a.album_year) ASC, a.album_name ASC) AS oai ON oai.album_id = rel_artist_album.album_id SET rel_artist_album.album_order = @num := @num +1 WHERE rel_artist_album.album_id = oai.album_id AND rel_artist_album.artist_id = 541;


    In most cases everything will be sorted right. But sometimes, if there are only very few rows to sort, the sorting goes wrong. If I take only the inner SELECT the value generated by the CASE is okay, but the UPDATE sorts the rows the other way.

    Lets say, I have three rows and the inner SELECT sorts them like this (ID - sort_col value):

    1. 7825 - 1
    2. 7826 - 1
    3. 7824 - 2


    But after the UPDATE the rows are ordered like this:

    1. 7824
    2. 7825
    3. 7826


    Is there anyone who faced a similar problem?
Working...
X