Numbering Rows on Output
I have a table that lists the tasks a program has to do. Lately I've found I can have an at-a-glance status report of how things are going on by writing a loop (in bash scripting, on Linux, btw) that uses mysql -e to display the list of tasks and their current state. It's quick and a lot simpler than I thought it would be to create a self-updating status display. The only thing missing is that it would be helpful to be able to add an extra column on the left for a row count -- preferably so each selected row has a number beside it, but putting a summary count on the last line (or adding an extra line with a summary count below it) would be helpful. I've Googled, but it seems this is almost impossible to do. Is it? Or is there a simple way to have a count next to the rows being displayed? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Numbering Rows on Output
In the last episode (Nov 28), Hal Vaughan said: I have a table that lists the tasks a program has to do. Lately I've found I can have an at-a-glance status report of how things are going on by writing a loop (in bash scripting, on Linux, btw) that uses mysql -e to display the list of tasks and their current state. It's quick and a lot simpler than I thought it would be to create a self-updating status display. The only thing missing is that it would be helpful to be able to add an extra column on the left for a row count -- preferably so each selected row has a number beside it, but putting a summary count on the last line (or adding an extra line with a summary count below it) would be helpful. I've Googled, but it seems this is almost impossible to do. Is it? Or is there a simple way to have a count next to the rows being displayed? SET @row=0; SELECT @row:[EMAIL PROTECTED] AS row, otherfields from mytable; If you're doing it with mysql -e, this does it with one command: SELECT @row:=(ifnull(@row,0))+1 AS row, otherfields from mytable; -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Numbering Rows on Output
On Monday 28 November 2005 04:45 pm, Dan Nelson wrote: In the last episode (Nov 28), Hal Vaughan said: I have a table that lists the tasks a program has to do. Lately I've found I can have an at-a-glance status report of how things are going on by writing a loop (in bash scripting, on Linux, btw) that uses mysql -e to display the list of tasks and their current state. It's quick and a lot simpler than I thought it would be to create a self-updating status display. The only thing missing is that it would be helpful to be able to add an extra column on the left for a row count -- preferably so each selected row has a number beside it, but putting a summary count on the last line (or adding an extra line with a summary count below it) would be helpful. I've Googled, but it seems this is almost impossible to do. Is it? Or is there a simple way to have a count next to the rows being displayed? SET @row=0; SELECT @row:[EMAIL PROTECTED] AS row, otherfields from mytable; If you're doing it with mysql -e, this does it with one command: SELECT @row:=(ifnull(@row,0))+1 AS row, otherfields from mytable; Actually, this kept printing 1 for each row, so I did this: mysql -e SET @row=0; SELECT @rwo:[EMAIL PROTECTED] AS Row... which, obviously, is using the first way, but setting the variable in the same command line in mysql -e. That worked perfectly! It's interesting how there were so many pages on the net that indicated this was not possible or easily done. Thank you! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Numbering rows
In article [EMAIL PROTECTED], Mauricio Pellegrini [EMAIL PROTECTED] writes: Wow, that's simply magic!!! You couldn't imagine how many diferent things I've tried to solve this problem.. And when I thought it was impossible ...your solution worked just fine at once! God bless experienced people!! The reason for trying to do such a weird thing on col_type is that a needed something to use as a pivot condition I'll explain a little further; thanks to you now, I have this in table tbl --- Id xorder item value col_type --- 13 15 0 1 23 15 5 2 33 15 0 3 48 22 7 1 58 22 0 2 610 64 20 1 --- Then I can run this query against tbl SELECT xorder, item, if( col_type=1,value , 0 ) as 'Hon', if( col_type=2,value , 0 ) as 'Gas', if( col_type=3,value , 0 ) as 'Other' FROM tbl GROUP BY xorder, item Which would deliver this final result xorder item Hon Gas Other 3 15 0 5 0 8 22 7 0 0 10 64 20 0 0 There would never be more than three rows for each xorder,item group but, of course there could be less. How do you distinguish between Hon and Gas? When you delete the line with ID 1 and recreate col_type, Gas all of a sudden becomes Hon. Smells like a horribly broken table design. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Numbering rows
In article [EMAIL PROTECTED], Mauricio Pellegrini [EMAIL PROTECTED] writes: This is the table I have Column Id is primary key and auto_numeric - Idorder itemvalue col_type - 1 3 15 0 null 2 3 15 5 null 3 3 15 0 null 4 8 22 7 null 5 8 22 0 null 6 10 64 20 null - And this is the result I would like to obtain. Where column col_type should be filled with a number representing the ordinal number for the row within the group formed by order,item - Idorder itemvalue col_type - 1 3 15 0 1 --This is row number 1 in the group 2 3 15 5 2 formed by Order 3 and Item 15 3 3 15 0 3 4 8 22 7 1 -This is row number 1 5 8 22 0 2 -This is row number 2 6 10 64 20 1 -This is row number 1 - (I've renamed order to xorder since ORDER is a reserved word.) You could use the old MyISAM/AUTO_INCREMENT trick: CREATE TEMPORARY TABLE tmp ( id INT UNSIGNED NOT NULL, xorder INT UNSIGNED NOT NULL, item INT UNSIGNED NOT NULL, col_type INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (xorder, item, col_type) ) TYPE=MyISAM; INSERT INTO tmp (id, xorder, item) SELECT id, xorder, item FROM tbl; UPDATE tbl, tmp SET tbl.col_type = tmp.col_type WHERE tbl.id = tmp.id; When you use an AUTO_INCREMENT column as the last part of a PRIMARY KEY, you'll get a new sequence for all combinations of the other key parts - but only for MyISAM tables. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Numbering rows
Wow, that's simply magic!!! You couldn't imagine how many diferent things I've tried to solve this problem.. And when I thought it was impossible ...your solution worked just fine at once! God bless experienced people!! The reason for trying to do such a weird thing on col_type is that a needed something to use as a pivot condition I'll explain a little further; thanks to you now, I have this in table tbl --- Id xorder item value col_type --- 1 3 15 0 1 2 3 15 5 2 3 3 15 0 3 4 8 22 7 1 5 8 22 0 2 6 10 64 20 1 --- Then I can run this query against tbl SELECT xorder, item, if( col_type=1,value , 0 ) as 'Hon', if( col_type=2,value , 0 ) as 'Gas', if( col_type=3,value , 0 ) as 'Other' FROM tbl GROUP BY xorder, item Which would deliver this final result xorder item Hon Gas Other 3 15 0 5 0 8 22 7 0 0 10 64 20 0 0 There would never be more than three rows for each xorder,item group but, of course there could be less. That's it. Thank you very much for your valuable help Mauricio On Fri, 2005-05-13 at 06:22, Harald Fuchs wrote: In article [EMAIL PROTECTED], Mauricio Pellegrini [EMAIL PROTECTED] writes: This is the table I have Column Id is primary key and auto_numeric - Id order itemvalue col_type - 1 3 15 0 null 2 3 15 5 null 3 3 15 0 null 4 8 22 7 null 5 8 22 0 null 6 10 64 20 null - And this is the result I would like to obtain. Where column col_type should be filled with a number representing the ordinal number for the row within the group formed by order,item - Id order itemvalue col_type - 1 3 15 0 1 --This is row number 1 in the group 2 3 15 5 2 formed by Order 3 and Item 15 3 3 15 0 3 4 8 22 7 1 -This is row number 1 5 8 22 0 2 -This is row number 2 6 10 64 20 1 -This is row number 1 - (I've renamed order to xorder since ORDER is a reserved word.) You could use the old MyISAM/AUTO_INCREMENT trick: CREATE TEMPORARY TABLE tmp ( id INT UNSIGNED NOT NULL, xorder INT UNSIGNED NOT NULL, item INT UNSIGNED NOT NULL, col_type INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (xorder, item, col_type) ) TYPE=MyISAM; INSERT INTO tmp (id, xorder, item) SELECT id, xorder, item FROM tbl; UPDATE tbl, tmp SET tbl.col_type = tmp.col_type WHERE tbl.id = tmp.id; When you use an AUTO_INCREMENT column as the last part of a PRIMARY KEY, you'll get a new sequence for all combinations of the other key parts - but only for MyISAM tables. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Numbering rows
Hi , I don't know if this is possible with Sql but I'm trying to set the row number into a field for each row. The complexity comes when I try to do that according to some grouping rules. I think I'll made myself more clear with a simple example: This is the table I have Column Id is primary key and auto_numeric - Id order itemvalue col_type - 1 3 15 0 null 2 3 15 5 null 3 3 15 0 null 4 8 22 7 null 5 8 22 0 null 6 10 64 20 null - And this is the result I would like to obtain. Where column col_type should be filled with a number representing the ordinal number for the row within the group formed by order,item - Id order itemvalue col_type - 1 3 15 0 1 --This is row number 1 in the group 2 3 15 5 2 formed by Order 3 and Item 15 3 3 15 0 3 4 8 22 7 1 -This is row number 1 5 8 22 0 2 -This is row number 2 6 10 64 20 1 -This is row number 1 - I hope it is clear enough All sugestions are welcome Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
