1

I have a MySQL table of the form...

record    timestamp
1         2014-07-10
2         2014-07-10
1         2014-07-11
2         2014-07-11
1         2014-07-12
2         2014-07-12

...and I want to query this in such a way that I return a set of the form...

record    1st time    2nd time    3rd time
1         2014-07-10  2014-07-11  2014-07-12
2         2014-07-10  2014-07-11  2014-07-12
...

I'm comfortable using MAX() and a subquery to achieve the most-recent timestamp, but this seems more suited to returning an iterable SQL object. For instance, if I could create the above with something like...

SELECT record, timestamp[0] AS "1st time", timestamp[1] AS "2nd time", timestamp[2] AS "3rd time"

...that would be great. Is this something PIVOT() could be used for?

Daniel Black
  • 968
  • 1
  • 7
  • 11
  • Join the table to itself 3 times. Messy, but SQL shapes are not dynamic (and dynamic query generation or external code must be used to handle N). If a string can be returned/processed MySQL *does* have [`group_concat`](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat). – user2864740 Aug 28 '14 at 21:25

1 Answers1

0

For the above data set limited to 3 columns you can do like below

SELECT t1.record,
MAX(CASE WHEN t1.rownum = 1 THEN t1.timestamp  END) AS `1st time`,
MAX(CASE WHEN t1.rownum = 2 THEN t1.timestamp  END) AS `2nd time`,
MAX(CASE WHEN t1.rownum = 3 THEN t1.timestamp  END) AS `3rd time`
FROM (
  SELECT t.*,
  @r:= CASE WHEN @g= record THEN @r + 1 ELSE 1 END rownum,
  @g:=record
  FROM table t
  CROSS JOIN (SELECT @g:=0,@r:=0) t1
  ORDER BY `record`, `timestamp`
  ) t1
GROUP BY t1.record

Demo

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • Thank you. Unfortunately, I won't know the position of the any of the entries and need to infer it by comparison. For instance, I currently use `MAX(t.timestamp)` and `ORDER BY t.timestamp DESC` to return the most-recent timestamp. I'm trying to generalize this to second, third, etc. positions. – Daniel Black Aug 29 '14 at 00:32
  • @DanielBlack no that not how mysql works, as standard there should be a defined no. of columns in query but if you need pivot there are some work around so dynamic pivot query but again there is a limitation to use group_concat and its length in dynamic sql, for the part you are saying max and order by then yes you can use your query as `select MAX(t.timestamp) times,record from table group by record order by times desc limit 1` for the highest ,for second highest change limit to limit 1,1 for 3rd highest limit 2,1 and so on but again you have to provide values for limited no.s in limit – M Khalid Junaid Aug 29 '14 at 11:57
  • The query will have a defined number of columns. I tried to simplify for the sake of legibility. I will return a record and the most recent, second-most-recent, ..., 90th most-recent timestamp for a given status. I'm getting the impression I might better just pull all timestamps for a moving 90-day window and do the ordering in Excel. Even if I can make the query do the work, unless I make it a view or stored procedure, it's going to be quite heavy. – Daniel Black Aug 29 '14 at 13:18
  • @DanielBlack i would suggest no to do this with dynamic query i can replicate above solution with a dynamic as [*`answered here`*](http://stackoverflow.com/a/25411480/853360) but this will not be an optimal solution ,no ways to optimize the solution for better performance its better to achieve such *90th most-recent timestamp* requirement at your application level code or something other hope i have made a clear view for you – M Khalid Junaid Aug 29 '14 at 14:47