Display of line / row numbers

I am new to HeidiSQL, I would like to see row ID's, in the query result pane.

Is this already possible? (if so, sorry I couldn't find the setting to enable it). If not, please could this be regarded as a feature request?

See attached file for clarification.

Thanks, Mathijs

No, there is no row id column, and there is no setting to show that.

But while the grid is focused, the status bar shows a panel with "[line] : [column]".

Hi, thanks for the reply.

Can I bring this in as a feature request?

That wouldn't be a row ID anyway. It'd be the row position in the result set. Is that what you want?

Yes, actually it doesn't really matter what name it gets..

I am looking for a extra column which contains row numbers. Similar as in Excel for example:

@mgroen:, what you see in Excel is not applicable in database result sets.

If you fire an SQL SELECT command which returns 20 rows, numbering them 01-20 would be completly useless because you would not be able to do anything with those numbers as they are not the true index pointers belonging to the corresponding data rown.

"Excel index method =/= database index method"

@TTsneko:

incorrect assumption. For me, as a QA/test engineer, the usage of a SQL tool would be to validate that, in your specific example, actually 20 rows are returned (if 20 would be expected). It would be much easier to visual validate if the row numbers would be displayed. The comparision to Excel is just to illustrate the possible implementation in Heidi's UI. Other than that I do not compare Excel with database result sets.

@mgroen:

Then why don't you rely on the numbers clearly shown already? The DATA view in your first screenshot clearly states "70 rows" (in this case). That count is always at the same place and would not require scrolling down if the result is larger. An additional numerical column next to the data rows adding up to number "X" would merely create unwanted visual clutter and even be potentially dangerous while working with true data indexes of the same number range (for obvious reasons). In a QUERY view, the corresponding tab consists of the info "tablename (columns x rows)" which also allows you to instantly see if the expected number of rows was returned. Again without having to scroll or look of the last result line.

I must really be missing something important here, otherwise I can only assume that you are trying to make things more complicated than they need to be.

@TTSneko:

relying on the value "70 rows" only allows me to validate the total amount of records. What I want to have is to see (visually) that value X is indeed on row 34 (for example). There is now way to do that now.

It wouldn't be a bad feature anyway. Some times I'm inspecting a result set back and forth and it could be handy to have a simple row number to spot the tuple again, esp. when the PK looks like 2,339,384 or (23, 85, 12).

... and it'd be even more useful if the column was fixed so it remained visible despite horizontal scroll.

@kalvaro: that's exactly what I mean (we have same usage scenario's)... Also.. totally agree with your remark in your last posting..

Also. I want to note that MS SQL Management Server (2012) has exactly this feature already (but still I like HeidiSQL, and want to help make it even better :))

Today this feature is still needed and missing. Eaerlier, when I tried to insert some data from the query window, it return an error: / SQL Error (1264): Out of range value for column 'id' at row 443 / So I though the problem was in the 443 query line 443, but indeed was is the 443 row of the data result set. Figuring out how to look for the line 443 brough me to this forum. We should have a feature for turn on/off line numbers y "Data" Tab. Same way as we have line numbers in "Query" tabs

Coming from MS SQL Management studio, I really miss this feature. I would not imagine it would take much time to implement, and it would be very useful during data compares.

It is possible to add line numbers by using a variable, or if your MySQL/MariaDB version has the ROW_NUMBER window function. (I believe in v10.2+ for MariaDB, not sure about MySQL)

This example for those using MariaDB/MySQL. Using a variable may differ in other DBs.

SELECT * FROM air_line_info;

code    sort
BR      0
CI      0
CX      0

Then by using a variable with the query:

SET @count = 0;

SELECT @count:=@count+1 AS 'line#', air_line_info.* FROM air_line_info;

Gives the output:

line# code sort
1     BR   0
2     CI   0
3     CX   0

I have a screenshot of the above example, and a fiddle using ROW_NUMBER, but I apparently do not yet have enough postings to add links.

This is related to issue #861. That ticket was meant for SQLite only, while I am just working for a general solution for all server types. Watch out for the next nightly builds if you want a static row id column.