mysql return multiple columns in subquery for previous and next in same table

Question

I am trying to concoct a mysql query that returns, in a single row, the fields for a given row, as well as a few fields from a row that matches the "previous" position, and the same fields for the "next" position. I'm pretty new at mysql, but for all the scouring the net for answers, this is the best I can do:

SELECT *, 
(select id
    from mytable t2
    where t2.added_on < t.added_on
    order by t2.added_on DESC
    limit 1
   ) as prev_id,
 (select id
    from  mytable t3
    where t3.added_on > t.added_on
    order by t3.added_on
    limit 1
   ) as next_id  FROM mytable as t ORDER BY `added_on`

which works, but only gives me the id field for the "previous" and "next". As you may know, using * (or 'id', 'title') instead of id in the subqueries gives an error. I've looked into using JOINs and some other approaches but I'm just simply not getting it.

Why don't you just get 3 rows? I mean 3 queries... Or use `UNION` like here: http://stackoverflow.com/a/20849223/2737474 — Gerardo Charles Rojas Vega, Oct 30 '14 at 21:00
@CharlesRojas, how would that work in my case? I tried the obvious approach, but that didn't work. — insaner, Oct 30 '14 at 21:27
I checked the docs, doesn't `UNION` return the results as different rows? Instead of on the same row? I would like my result to be on the same row, if possible. — insaner, Oct 30 '14 at 21:38
I would really like to know the reason you want the result set in a single row... there's maybe another option — Gerardo Charles Rojas Vega, Oct 31 '14 at 11:40
If you want the result in a single row you can use the same query just adding the fields you want in the subqueries — Gerardo Charles Rojas Vega, Oct 31 '14 at 11:56
I just figure it's doable, and as such it is probably optimized, and one call to execute it should be faster than 3 (or per field). If it isn't possible I will just try a different approach. — insaner, Oct 31 '14 at 13:46
What you want can be done, but is not the best option since there's no benefit... as I can see (3 or 1 row not makes a difference of time)... if you want a optimized way you must focus on the query how complex (take more time) or simple (take less time) it looks, and in one query if possible... the size of result set doesn't affect — Gerardo Charles Rojas Vega, Oct 31 '14 at 15:42

score 0 · Answer 1 · answered Nov 02 '14 at 18:19

OK, I figured it out, here is the solution for all of you out there trying to find a solution to this. Make sure to vote up if it helped you.

SELECT t.*, 
  prev.id as prev_id,
  prev.added_on as prev_added_on, 
  next.id as next_id,
  next.added_on as next_added_on
FROM `TABLE_NAME` AS t
LEFT JOIN `TABLE_NAME` AS prev
ON prev.id =  
   (select id
    from `TABLE_NAME` t2
    where t2.added_on < t.added_on
    order by t2.added_on DESC
    limit 1) 
LEFT JOIN `TABLE_NAME` AS next
ON next.id =  
   (select id
    from `TABLE_NAME` t3
    where t3.added_on > t.added_on
    order by t3.added_on
    limit 1 )
 ORDER BY t.added_on DESC

That last ORDER BY actually causes the whole thing to be greatly optimized for some reason (my current understanding of mysql doesn't let me know why this is for sure) and in my case it makes execution at least twice as fast as without it.

mysql return multiple columns in subquery for previous and next in same table

1 Answers1