0

I have a table like this:

startyearmonthday| id
20130901         |  1
20131004         |  2
20130920         |  3
20131105         |  4
20131009         |  5

I want to write a query where I can return a table like this:

startyearmonthday| id  | endyearmonthday
20130901         |  1  | 20130920
20130920         |  3  | 20131004
20131004         |  2  | 20131009
20131009         |  5  | 20131105
20131105         |  4  | null

So I want the end date based on the next earliest start date after the current start date. I imagine some sort of join is involved but I can't figure it out...

SimaPro
  • 1,164
  • 4
  • 14
  • 28
  • Possible duplicate of http://stackoverflow.com/questions/1446821/how-to-get-next-previous-record-in-mysql – PM 77-1 Sep 01 '13 at 20:40
  • I did see that, and I saw that you mentioned that in my previous question, but this is different because I do want a single row to return the current start AND the next start as the end. So in that link it would be like not just finding the next id, but also returning what the ID after that would be in the same table somehting like: "id | DATA | nextID". I'm pretty sure I need some sort of subquery, where I ask for "start, data, and min(start)>start as end" or someting like that... – SimaPro Sep 01 '13 at 20:46
  • You can use the answer I provided here http://stackoverflow.com/questions/18541451/mysql-conditions-from-multiple-rows/18543078?noredirect=1#comment – Raúl Juárez Sep 01 '13 at 21:01
  • Thanks @RaúlJuárez, but that's quite an involved query; I think this example requires something much more simple.. – SimaPro Sep 01 '13 at 21:05

2 Answers2

1

Try this (assuming there are no repeated rows):

select a.*, b.startyearmonthday as endyearmonthday
from table_name a
left join table_name b
on b.startyearmonthday > a.startyearmonthday and not exists(select startyearmonthday from table_name c where a.startyearmonthday < c.startyearmonthday and c.startyearmonthday < b.startyearmonthday)
Miquel
  • 858
  • 11
  • 20
1

I would be inclined to do this with a correlated subquery:

select t.*,
       (select startyearmonthday
        from t t2
        where t2.startyearmonthday > t.startyearmonthday
        order by t2.startyearmonthday
        limit 1
       ) as endyearmonthday
from t;

As with your earlier question, this will run pretty quickly with an index on t(startyearmonthday).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! I ended up using the following as my query: "select times.*, (select min(start) from times t2 where t2.start>times.start) as end from times" Do you see any issues with using min() instead of the way you structured yours? Anyway, marking this as the accepted answer because it got me where I needed to go... Also - where do I go to learn to call things like "Correlated subquery" etc...? – SimaPro Sep 01 '13 at 21:07