Display of line / row numbers

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago in General Permalink

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

1 attachment(s):
  • 2016-10-19_14-43-44
ansgar's profile image ansgar posted 7 years ago Permalink

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]".

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

Hi, thanks for the reply.

Can I bring this in as a feature request?

kalvaro's profile image kalvaro posted 7 years ago Permalink

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

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

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:

1 attachment(s):
  • 2016-10-20_9-17-09
TTSneko's profile image TTSneko posted 7 years ago Permalink

@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"

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

@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.

TTSneko's profile image TTSneko posted 7 years ago Permalink

@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.

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

@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.

kalvaro's profile image kalvaro posted 7 years ago Permalink

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).

kalvaro's profile image kalvaro posted 7 years ago Permalink

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

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

@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 :))

roy-marquez's profile image roy-marquez posted 3 years ago Permalink

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

kesse's profile image kesse posted 3 years ago Permalink

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.

PaulT's profile image PaulT posted 2 years ago Permalink

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.

ansgar's profile image ansgar posted 5 months ago Permalink

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.

Description

Please login to leave a reply, or register at first.