Display Row Number in MYSQL query

Question

I have this sql query:

SET @row_num=0; 
SELECT @row_num:=@row_num+1 as 'Num', book_title, author_name
FROM books

When I test this query in phpmyadmin, it show result perfectly but when I test it in Pentaho User Console, it show error.

Can anyone tell me what is wrong with my query? Is there any other way I can fix it so that it will show the result perfectly in Pentaho?

http://stackoverflow.com/questions/431053/what-is-the-best-way-to-generate-ranks-in-mysql — Ciro Santilli OurBigBook.com, Oct 07 '15 at 05:29

score 3 · Answer 1 · answered Oct 07 '14 at 07:02

3

Try this code.

SET @row_number:=0;
SELECT @row_number:=@row_number+1 AS row_number,book_title, author_name FROM books;

It might help you.

answered Oct 07 '14 at 07:02

Phoenix

BlitZ · Answer 2 · 2014-02-14T07:13:51.747

2

Try to build a single query, like this:

SELECT
    @row_num := @row_num + 1 as 'Num',
    book_title,
    author_name
FROM
    books JOIN (SELECT @row_num := 0 FROM DUAL) as sub;

The problem currently (I guess), that you have two statements instead of one:

SET @row_num=0;
-- ^ statement #1

SELECT @row_num:=@row_num+1 as 'Num', book_title, author_name
FROM books;
-- ^ statement #2

So try to mix them into one statement, like I mentioned above. For MySQL it is a legal syntax.

edited Feb 14 '14 at 07:13

answered Feb 14 '14 at 07:03

BlitZ

I try built like you show but it show error not just in Pentaho but also in phpmyadmin. – crystal Feb 14 '14 at 07:09
@user3106393 could you provide an error description / error message? – BlitZ Feb 14 '14 at 07:09
it say I have an error in my sql syntax near 'sub'; – crystal Feb 14 '14 at 07:15
@user3106393 updated query. Try it now. And yeah, what MySQL Server version are you using ? – BlitZ Feb 14 '14 at 07:17
When I test your updated query, it show the result in both platform (PHPMYADMIN & pentaho) but the row_num result is wrong. The 1st row it display as 1 but the second row is 3. I want it show as 1,2,3....I use MySQL version 5 – crystal Feb 14 '14 at 07:24
@user3106393 Thats... Weird... It shouldn't be. Undate question with output results. In MySQL Console I'm not able to reproduce this issue. – BlitZ Feb 14 '14 at 07:28
Huhuhu..if that so, thank you so much for your help. I'll try to figure it out myself. Thanks! – crystal Feb 14 '14 at 07:34
Other than this type of query, do you know how to use row_number()? I see some examples but I don't know how to apply it in my query – crystal Feb 14 '14 at 07:47
@user3106393 MySQL has no `ROW_NUMBER()` function. Unfortunately. See [this](http://stackoverflow.com/questions/1895110/row-number-in-mysql). – BlitZ Feb 14 '14 at 07:50
Oh, I see. No wonder when I use it I always get an error every time I run my query. – crystal Feb 14 '14 at 08:07

2 Answers2