How to add an extra row at end of query with value of (previousRowColA + 1) in mysql?

Question

I have a MySQL table with data and I'm selecting out the "rank":

SELECT (@rank:=@rank+1) AS 'value' FROM MYTable a INNER JOIN (SELECT @rank :=0) b

This returns:

VALUE
1
2
3
4

Because there are 4 rows. But I want a query that can return me the results with one extra row (i.e. n+1, where n == totalNumberRows):

VALUE
1
2
3
4
5

How would I do this?

@Hardy Thank you, that's correct. See TimBurch's answer below. — Don Rhummy, Mar 07 '14 at 18:10
`UNION ALL` is probably better since it has less overhead and you actually want all rows. — Joachim Isaksson, Mar 07 '14 at 18:10
@JoachimIsaksson it appears for performance that `UNION ALL` is better, thank you: http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all — Don Rhummy, Mar 07 '14 at 18:13

Tim Burch · Accepted Answer · 2014-03-07T18:14:37.883

3

How about a UNION query:

SELECT (@rank:=@rank+1) AS 'value'
FROM MYTable a
    INNER JOIN (SELECT @rank :=0) b
UNION ALL
SELECT (@rank+1);

edited Mar 07 '14 at 18:14

answered Mar 07 '14 at 18:08

Tim Burch

Thank you! That's the answer. I will mark it in 11 minutes when Stack lets me. – Don Rhummy Mar 07 '14 at 18:09
2

But avoid `UNION` when it can be replaced with `UNION ALL` operation. `UNION` causes grouping, `UNION ALL` - doesn't. – zealot Mar 07 '14 at 18:09
@zealot How would a "UNION ALL" change that query? Would it still work as I want? – Don Rhummy Mar 07 '14 at 18:10
`UNION` is equivalent of `select distinct rows from .. union all ..` – zealot Mar 07 '14 at 18:12
@zealot OK, so performance is better with `UNION ALL` thank you! – Don Rhummy Mar 07 '14 at 18:14
@zealot Thanks for the reminder about UNION ALL. I've edited my answer accordingly. – Tim Burch Mar 07 '14 at 18:15

1 Answers1