GROUP BY - CONCAT query question?
Hi, You can try the following. Store the result of your query into a temporary table, say tp, with two columns tc (ticketID) and pname (pipelineName). Run the following query : select concat(select @c:='';,select tc, (@c:=concat_ws(,,@c,pname)) as x, length(@c) from tp where tc=, tc, order by 3 desc limit 1;) from tp group by tc ; and store the result into another variable (say, sth). Execute the query in sth. You should get your results as the column x in the result set. SB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
GROUP BY - CONCAT query question?
Hi all. 3 tables. Ticket Pipeline_Dept, and Ticket_Matrix. Ticket_Matrix matches tickets to pipeline depts. A ticket can be posted to multiple pipeline depts. I want to select all tickets and the pipeline departments they are posted to but only one row per ticket. So: SELECT t.ticketID, p.pipelineName FROM Ticket t LEFT JOIN Ticket_Matrix tm ON t.ticketID = tm.ticketID LEFT JOIN Pipeline_Dept p ON p.pipelineID = tm.pipelineID This is what I want: +--+--+ | ticketID | pipelineName | +--+--+ | 163 | IT, Adv.Tech, RD| | 164 | IT, RD | | 165 | Video, Multimedia| | ... | ... | +--+--+ This is what I get now: +--+--+ | ticketID | pipelineName | +--+--+ | 163 | IT | | 163 | Adv.Tech | | 163 | RD | | 164 | IT | | 164 | RD | | 165 | Video| | 165 | Multimedia | | ... | ... | +--+--+ I cannot do it with PHP because my query depends on LIMIT and returning a set number of rows. If I use PHP to do the concat-ing, the number of rows may be LIMIT. I appreciate any help. kp - This mail sent through IMP: http://horde.org/imp/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
group by concat
Hi! My problem is when I use group by concat(col1,col2) when col1 and col2 is primary keys and col1 is varchar(255): SELECT col1,col2 FROM Table1 GROUP BY concat(col1,col2); - return only one row - it is bad SELECT col1,col2 FROM Table1 GROUP BY concat(col1,col1,col2); - return 48 rows - it is ok, but concat(col1,col1,col2) is stupid I change col1 to varchar(155): SELECT col1,col2 FROM Table1 GROUP BY concat(col1,col2); - return 48 rows - it is ok My platform: Linux x86, MySQL 3.23.39, table is myisam format. Is it bug or feature? Thanks Marek - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: group by concat
Hi! My problem is when I use group by concat(col1,col2) when col1 and col2 is primary keys and col1 is varchar(255): SELECT col1,col2 FROM Table1 GROUP BY concat(col1,col2); - return only one row - it is bad SELECT col1,col2 FROM Table1 GROUP BY concat(col1,col1,col2); - return 48 rows - it is ok, but concat(col1,col1,col2) is stupid I change col1 to varchar(155): SELECT col1,col2 FROM Table1 GROUP BY concat(col1,col2); - return 48 rows - it is ok My platform: Linux x86, MySQL 3.23.39, table is myisam format. Is it bug or feature? Thanks Marek Sir, what does GROUP BY Concat(col1, col2) do that GROUP BY col1, col2 does not? In other words, I don't see any point in using Concat(). Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
