mysql动态设置查询的列_MySQL：动态添加列以查询结果

I have this table:

update_id | project_id | content | date

------------------------------------------------------

1 | 1 | text... | 2011-12-20 22:10:30

2 | 2 | text... | 2011-12-20 22:10:30

3 | 2 | text... | 2011-12-21 22:10:30

4 | 2 | text... | 2011-12-22 22:10:30

5 | 2 | text... | 2011-12-23 22:10:30

To get the latest two updates for a specific project I use:

SELECT update_id, title, content, date

FROM updates

WHERE project_id = 2

ORDER BY date DESC

LIMIT 2

Now, I want to dynamically add a 'update_time' column to the results, with the values "LATEST" or "PREVIOUS" based on if it is the latest update or the one before that,

like this:

update_time | update_id | content | date

------------------------------------------------------

LATEST | 5 | text... | 2011-12-23 22:10:30

PREVIOUS | 4 | text... | 2011-12-22 22:10:30

解决方案

Here's some smart-ass SQL. It select 'LATEST' for the first row, and 'PREVIOUS' for all others (should we have more than one of them).

SELECT IF(@rownum = 0, 'LATEST', 'PREVIOUS') update_time, update_id,

title, content, date, (@rownum := @rownum + 1) r

FROM updates, (SELECT @rownum := 0) dummy

WHERE project_id = 2

ORDER BY date DESC

LIMIT 2

Also it adds another column to the resultset. Hope it's not a problem.