In a little app where users can create tabs and portlets in those tabs I ran into some issues with my ordered list implementation. It turns out I just misplaced a filtering expression in the WHERE clause instead of the JOIN condition. The basic idea was that I wanted to add new portlets in the left hand column at the top. The positions from the portlets actually start at 0, but what I am doing is to insert them at 1 below the current minimum or -1 if the table is empty. Then in a second query I push everything up by one. Seemed like the most efficient way to deal with concurrency. I guess in theory I could even skip pushing things up by one, but oh well.

Here is the original query (note that I am using PDO which supports Oracle style named placeholders for all drivers including MySQL):

INSERT INTO user_tab_portlets (tab_id, portlet_id, pos, col, is_open, config)
            (SELECT :tab_id, :portlet_id, COALESCE(MIN(utp.pos)-1, -1), :col, :is_open, :config
                FROM user_tabs ut LEFT JOIN user_tab_portlets utp ON (ut.id = utp.tab_id)
                WHERE ut.id = :tab_id AND (utp.col IS NULL OR utp.col = :col)
                GROUP BY utp.tab_id, utp.col)

I sort of expected the "utp.col IS NULL OR utp.col = :col" to work out fine for the case when there are no portlets yet in the left column on the given tab, since due to the LEFT JOIN the "IS NULL" part should fire (the col is not nullable). As it turns out a more readable and actually working version of my query would look like this:

INSERT INTO user_tab_portlets (tab_id, portlet_id, pos, col, is_open, config)
            (SELECT :tab_id, :portlet_id, COALESCE(MIN(utp.pos)-1, -1), :col, :is_open, :config
                FROM user_tabs ut LEFT JOIN user_tab_portlets utp ON (ut.id = utp.tab_id AND utp.col = :col)
                WHERE ut.id = :tab_id
                GROUP BY utp.tab_id, utp.col)

Anyways, if anyone has some pointers on how to better do an ordered list in SQL please let me know. I felt quite good when I figured this one out :)

the second version (with the condition in the LEFT JOIN clause) is better because the query optimizer can use a more efficient join method that touches more rows, plus it increases the chance that an index can be used for the filter AND the join.

(i think you first query is actually optimized in to the second query as of mysql 5.1)