Search code examples
mysqlordinal

SQL: Creating ordinal columns from return set

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?

Solution

  • 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