butlimous

Yup just discovered that kicken...thanks

Tried replacing COUNT(DISTINCT comments.id) with GROUP_CONCAT(comments.id) but it's still producing the same results as the count distinct

Hey Barand So should GROUP_CONCAT be used instead of group by at the bottom or COUNT should be put inside the groupconcat or what?? Thanks

MYSQL version: 5.5.27-cll - MySQL Community Server (GPL) Hey I have a simple articles and comments table. I want to display the articles along with their comments. I want to union with one select with the comments and another select without the comments on the same table. I have article number 1 with 1 comment and article number 2 with no comments and article number 3 with 2. I use the following query to get the results. SELECT articles.id AS article_id, comments.id AS comment_id, comment FROM articles LEFT JOIN comments ON comments.aid = articles.id UNION ALL SELECT articles.id AS article_id, NULL, NULL FROM articles GROUP BY article_id ORDER BY article_id DESC The result I get which is correct: article_id | comment_id | comment 3 | 3 | good 3 | 2 | very good 3 | NULL | NULL 2 | NULL | NULL 2 | NULL | NULL 1 | NULL | NULL 1 | 1 | | bad one Now if I want to count the comments also I add COUNT to the query and it becomes: SELECT articles.id AS article_id, comments.id AS comment_id, comment , COUNT(DISTINCT comments.id) AS count_comments FROM articles LEFT JOIN comments ON comments.aid = articles.id UNION ALL SELECT articles.id AS article_id, NULL, NULL , NULL FROM articles GROUP BY article_id ORDER BY article_id DESC Now the results change after adding the the count column and not all rows are outputted: article_id | comment_id | comment 3 | NULL | NULL 2 | NULL | NULL 1 | NULL | NULL 1 | 1 | bad one Now the comments aren't displayed except the comment of article 1, ID(2) should be displayed twice for the 2 select commands, and ID(3) should be displayed 3 times (1 for the second select command and 2 for the first select commands as there are 2 comments) I don't know why adding count leads to ths. Thanks

thanks Barand for your effort

Hey Do you think would it be better performance wise using the following subquery: SELECT MIN(id),MAX(id) FROM (SELECT id FROM articles ORDER BY id DESC LIMIT 10) t Or using 2 separate queries?? Thanks

Thanks....surprisingly very old bug!

Hey Jessica Which one? You mean the one after adding PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL ? I'm getting 11 and 10 (the reason for 10 is that using fetch another time will skip the previous results so the first time it fetched 11 and then at the second time it skips the 11th row and start from the 10th row) Thanks

Hey Thanks for your input. Tried it like this before with no avail also. I don't know if it has something to do with configuration of PHP or what. $aid = $DBH->prepare("SELECT id FROM kisses ORDER BY id DESC LIMIT 10", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); $aid->execute(); $row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST); $lowest_article_id = $row[0]; $row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_FIRST); $highest_article_id = $row[0]; I can also get the correct results using the following query with subquery: SELECT MIN(id),MAX(id) FROM (SELECT id FROM articles ORDER BY id DESC LIMIT 10) t However a lot of people advise against using subqueries on a large scale because they are performance hit.

Hey I have a simple articles table with IDs. I want to get the highest and lowest ids from the latest 10 results. For example, if there are 11 ids, the result should be 2 and 11 and if there are 4 ids, should be 4 and 1 and so on. I use PHP PDO statements. $aid = $DBH->prepare("SELECT id FROM articles ORDER BY id DESC LIMIT 10"); $aid->execute(); $row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST); $lowest_article_id = $row[0]; $row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_FIRST); $highest_article_id = $row[0]; The above will always return 11 if there are 11 records and 10 if there are 10 records. So, the query ignores the PDO::FETCH_ORI_LAST and PDO::FETCH_ORI_FIRST part of the query. Thanks