Re: View select results
>One last thing: you set, at first, a parameter called @prev with Null >(' ') value: right? No, I set it to a string containing one space char. Use anything that does not occur as data in the column. >And, after, you use, instead IF ELSE statement, another syntax: is it >trinary operator? if yes, why it not is in the online MySQL manual? I used the IF() function, see 'Control Flow Functions' under 'Functions and Operators' in the manual PB -- spacemarc wrote: 2007/5/2, Peter Brawley <[EMAIL PROTECTED]>: Right, give the computed column an alias differeing from the column name, eg SET @prev=''; SELECT IF(area = @prev, '', @prev := area) AS AreaHdr, text,amount FROM products ORDER BY area DESC; ok, now it works! thanks! One last thing: you set, at first, a parameter called @prev with Null (' ') value: right? And, after, you use, instead IF ELSE statement, another syntax: is it trinary operator? if yes, why it not is in the online MySQL manual? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
2007/5/2, Peter Brawley <[EMAIL PROTECTED]>: Right, give the computed column an alias differeing from the column name, eg SET @prev=''; SELECT IF(area = @prev, '', @prev := area) AS AreaHdr, text,amount FROM products ORDER BY area DESC; ok, now it works! thanks! One last thing: you set, at first, a parameter called @prev with Null (' ') value: right? And, after, you use, instead IF ELSE statement, another syntax: is it trinary operator? if yes, why it not is in the online MySQL manual? -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
Right, give the computed column an alias differeing from the column name, eg SET @prev=''; SELECT IF(area = @prev, '', @prev := area) AS AreaHdr, text,amount FROM products ORDER BY area DESC; PB spacemarc wrote: 2007/5/2, Peter Brawley <[EMAIL PROTECTED]>: Works for me. Please post a CREATE TABLE stmt & enough INSERTs to demonstrate the problem. This is the dump (MySQL: 5.0.38): the table is not final version, just to test the query. CREATE TABLE `products` ( `area` varchar(25) NOT NULL, `text` varchar(25) NOT NULL, `amount` int(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `products` (`area`, `text`, `amount`) VALUES ('area1', 'some text', 12), ('area1', 'other text here', 13), ('area3', 'example...', 22), ('area2', 'things', 123), ('area1', 'bla bla...', 24), ('area2', 'others again', 231), ('area1', 'english language..', 44), ('area1', 'server database', 53), ('area3', 'php language...', 22), ('area2', 'linux box', 951), ('area1', 'developer tools', 4), ('area2', 'others words', 1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
2007/5/2, Peter Brawley <[EMAIL PROTECTED]>: Works for me. Please post a CREATE TABLE stmt & enough INSERTs to demonstrate the problem. This is the dump (MySQL: 5.0.38): the table is not final version, just to test the query. CREATE TABLE `products` ( `area` varchar(25) NOT NULL, `text` varchar(25) NOT NULL, `amount` int(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `products` (`area`, `text`, `amount`) VALUES ('area1', 'some text', 12), ('area1', 'other text here', 13), ('area3', 'example...', 22), ('area2', 'things', 123), ('area1', 'bla bla...', 24), ('area2', 'others again', 231), ('area1', 'english language..', 44), ('area1', 'server database', 53), ('area3', 'php language...', 22), ('area2', 'linux box', 951), ('area1', 'developer tools', 4), ('area2', 'others words', 1); -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
>can I obtain a recordset like this? >area1, value_one, thing_one > //, value_two, thing_32 > //, value_three, thing_ dd >area2, value_ten, thing_6w > //, value_ff, thing_l SET @prev=''; SELECT IF(area = @prev, '', @prev := area) AS area, ... other columns ... FROM &c ... PB spacemarc wrote: Hi my table have three fields that, if selected, are shown like: area1, value_one, thing_one area1, value_two, thing_32 area1, value_three, thing_ dd area2, value_ten, thing_6w area2, value_ff, thing_l can I obtain a recordset like this? area1, value_one, thing_one //, value_two, thing_32 //, value_three, thing_ dd area2, value_ten, thing_6w //, value_ff, thing_l So, do not repeat more times the value of the first column (area1, area2...) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View select results
Hi, spacemarc wrote: Hi my table have three fields that, if selected, are shown like: area1, value_one, thing_one area1, value_two, thing_32 area1, value_three, thing_ dd area2, value_ten, thing_6w area2, value_ff, thing_l can I obtain a recordset like this? area1, value_one, thing_one //, value_two, thing_32 //, value_three, thing_ dd area2, value_ten, thing_6w //, value_ff, thing_l So, do not repeat more times the value of the first column (area1, area2...) Giuseppe Maxia wrote a great article on this some time ago. The technique is called cross-tabulation or pivot tables. Here is a link: http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
View select results
Hi my table have three fields that, if selected, are shown like: area1, value_one, thing_one area1, value_two, thing_32 area1, value_three, thing_ dd area2, value_ten, thing_6w area2, value_ff, thing_l can I obtain a recordset like this? area1, value_one, thing_one //, value_two, thing_32 //, value_three, thing_ dd area2, value_ten, thing_6w //, value_ff, thing_l So, do not repeat more times the value of the first column (area1, area2...) -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]