RE: Group By Problem
You need to group by event_text, not obj_text: select source_id ,event_text,count(*) from event_loc group by source_id,event_text; Date: Tue, 8 Feb 2011 16:31:39 +0530 From: adarsh.sha...@orkash.com To: mysql@lists.mysql.com Subject: Group By Problem Dear all, I stuck around one more simple problem today. I have a table named *event_loc* having below data : ( It has many columns but I show you main columns that I needed ) _*Tables Data :-*_ *source_id event_text* 1233 meet 1233 meet 1345 ends 1345 ends performs 13456 Minister 1233 Argentina 1233 meet 1345 ends 1555 is As described above there are more than 10 rows and I want my output as : _*Output Needed :-*_ *source_id event_text Count* 1233 meet 3 1233 Argentina 1 1345 ends 3 performs 1 13456 Minister 1 I tried the below query : *select source_id ,event_text,count(*) from event_loc group by source_id,obj_text ;* But displays only unique record_id rows but I want as mentioned output. Failed to achieve the mentioned output. Thanks Regards Adarsh Sharma
Re: group by problem
kalin mintchev wrote: hi all... i have an issue with group by and ordering. apparently group by ignores 'order by id DESC'?! an example is a table that has an id and a category fields. there are a few categories under which records can be filed. so what i want is the latest record from each category by doing something like: select id, name from table group by category order by id DESC; this doesn;t work - it shows me the first record under each category - not the latest as specified by DESC?! something is wrong. i tried 'distinct' but that 'distincts' on all fields in the query?!?! whats the point of distinct if it can not distincts between fields?! in: select distinct category, id, name from table order by id DESC; this query distincts on all category, id and name when it should distinct only on category how do i do that without temporary tables? You misunderstand group by distinct and what they do. If you have some records: name - chris name - mary name - fred name - chris and run something like this: select name, count(*) from table group by name; it will return: chris - 2 mary - 1 fred - 1 *then* if you have an order by mysql will order the results that are returned by that query: select name, count(*) from table group by name order by name desc; it will return: mary - 1 fred - 1 chris - 2 Basically you can't do what you want either without temporary tables or using a subselect. Subselects are only available in mysql 4.1+ (I think - check the docs) so that may or may not be an option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by problem
Basically you can't do what you want either without temporary tables or using a subselect. Subselects are only available in mysql 4.1+ (I think - check the docs) so that may or may not be an option. thanks... pardon my ignorance - how would i do that using subselects? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by problem
kalin mintchev wrote: Basically you can't do what you want either without temporary tables or using a subselect. Subselects are only available in mysql 4.1+ (I think - check the docs) so that may or may not be an option. thanks... pardon my ignorance - how would i do that using subselects? Without knowing your schema this is just a guess but: select * from records where categoryid=(select categoryid from categories order by last_update desc limit 1); will get the most recently updated category and match it to the 'records' table. Actually... That could probably be done as a join: select * from records r inner join categories c on (r.categoryid=c.categoryid) order by c.last_update desc; but that will get you all records for that category not just the most recently updated. Of course I could be on the completely wrong track because you haven't fully described what you're trying to get out and what data you have ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by problem
but that will get you all records for that category not just the most recently updated. that's the main problem, isn't it? what i'm looking for is the last record for EACH of the categories in the table. i'm aware of the aformentioned options. my problem with group by is that ignores the DESC in order by. if i do: select id from table group by category; it does the same as if i do: select id from table group by category order by id desc; both queries return the FIRST found record for EACH category in ascending order - the order they were indexed in. i geuss i still don't get why if i request 'order by id desc' the 'group by' doesn't look 'backwards'. obviuosly the 'grouping' comes before the 'ordering' in the query - it probably is executed that way too. why can't it be the other way around taking in consideration the request of ordering the results in descending order and then grouping them by category i guess the answer is - just because... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by problem
On 10/16/06, kalin mintchev [EMAIL PROTECTED] wrote: but that will get you all records for that category not just the most recently updated. that's the main problem, isn't it? what i'm looking for is the last record for EACH of the categories in the table. i'm aware of the aformentioned options. my problem with group by is that ignores the DESC in order by. if i do: select id from table group by category; it does the same as if i do: select id from table group by category order by id desc; both queries return the FIRST found record for EACH category in ascending order - the order they were indexed in. i geuss i still don't get why if i request 'order by id desc' the 'group by' doesn't look 'backwards'. obviuosly the 'grouping' comes before the 'ordering' in the query - it probably is executed that way too. why can't it be the other way around taking in consideration the request of ordering the results in descending order and then grouping them by category The order by orders the *results* of the group by. It does not affect what happens inside the group by. This is standard across all databases - mysql, postgres, sqlite - all of them. It can't be done the other way because grouping the results together will affect ordering (think of aggregate functions especially). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By Problem
* Lightware Software Say I have the following table (TBL): KEY GRP VAL 1 A 2 2 A 3 3 A 1 4 B 2 5 B 1 6 B 3 select KEY, max(VAL) from TBL group by GRP gives: KEY max(VAL) 1 3 4 3 the desired result though is: KEY max(VAL) 2 3 6 3 any ideas on how to achieve this ? There is a special page in the manual for this problem, one of my favorite sql hacks is the MAX-CONCAT trick: URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Group by problem
Hi, I checked it with 3.23.54 on Windows and got the same incorrect results. Regards, Dmitry -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 11:30 PM To: [EMAIL PROTECTED] Subject: Re: Group by problem Dmitry, - Original Message - From: Dmitry Kosoy [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, December 12, 2002 8:02 PM Subject: Group by problem Hi, The following sql operators caused to incorrect results: CREATE TEMPORARY TABLE temp_list ( CurrID char(42) NOT NULL, OriginalID char(42) NOT NULL , DocRevision int(16) unsigned NOT NULL ) TYPE=InnoDB; insert into temp_list select CurrID, OriginalID, max(DocRevision) from some_table where DocRevision in (0,1,2,3) group by OriginalID having (sum(deleted) = 0); I revealed that temp_list table contains some incorrect values of CurrID (not matched to found OriginaID and Docrevision). According to Mysql documentation it should work. The values of CurrID is a primary key in some_table. These are the column definitions in some_table (InnoDB): CurrID char(42) NOT NULL, OriginalID char(42) NOT NULL , DocRevision int(16) unsigned NOT NULL . . . I check this in 3.51 and 3.53 and got the same results. Sinisa fixed 2 weeks ago a bug where HAVING(aggregate_function(...) = ...) did not work. Please test with 3.23.54! Regards, Dmitry Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify us immediately and delete this communication. - 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 problem
Dmitry, - Original Message - From: Dmitry Kosoy [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Sunday, December 15, 2002 7:11 PM Subject: RE: Group by problem Hi, I checked it with 3.23.54 on Windows and got the same incorrect results. ... The following sql operators caused to incorrect results: CREATE TEMPORARY TABLE temp_list ( CurrID char(42) NOT NULL, OriginalID char(42) NOT NULL , DocRevision int(16) unsigned NOT NULL ) TYPE=InnoDB; insert into temp_list select CurrID, OriginalID, max(DocRevision) from some_table where DocRevision in (0,1,2,3) group by OriginalID having (sum(deleted) = 0); I revealed that temp_list table contains some incorrect values of CurrID (not matched to found OriginaID and Docrevision). According to Mysql documentation it should work. The values of CurrID is a primary key in some_table. since you do not mention CurrID in the GROUP BY list, MySQL will return SOME value for it in the group. The manual does not say that the returned CurrID should necessarily be from the same row as Docrevision. But below you see a CONCAT trick which you can use to get a matching pair of DocRevision and CurrID. http://www.mysql.com/doc/en/Group_by_functions.html MySQL has extended the use of GROUP BY. You can use columns or calculations in the SELECT expressions that don't appear in the GROUP BY part. This stands for any possible value for this group. ... Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results. In some cases, you can use MIN() and MAX() to obtain a specific column value even if it isn't unique. The following gives the value of column from the row containing the smallest value in the sort column: SUBSTR(MIN(CONCAT(RPAD(sort,6,' '),column)),7) Regards, Dmitry Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - 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 problem
Dmitry, - Original Message - From: Dmitry Kosoy [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, December 12, 2002 8:02 PM Subject: Group by problem Hi, The following sql operators caused to incorrect results: CREATE TEMPORARY TABLE temp_list ( CurrID char(42) NOT NULL, OriginalID char(42) NOT NULL , DocRevision int(16) unsigned NOT NULL ) TYPE=InnoDB; insert into temp_list select CurrID, OriginalID, max(DocRevision) from some_table where DocRevision in (0,1,2,3) group by OriginalID having (sum(deleted) = 0); I revealed that temp_list table contains some incorrect values of CurrID (not matched to found OriginaID and Docrevision). According to Mysql documentation it should work. The values of CurrID is a primary key in some_table. These are the column definitions in some_table (InnoDB): CurrID char(42) NOT NULL, OriginalID char(42) NOT NULL , DocRevision int(16) unsigned NOT NULL . . . I check this in 3.51 and 3.53 and got the same results. Sinisa fixed 2 weeks ago a bug where HAVING(aggregate_function(...) = ...) did not work. Please test with 3.23.54! Regards, Dmitry Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - 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 problem.
Sir, look up Rand and ORDER BY in the documentation. I believe there's an example of using Rand and ORDER BY to do this. Bob Hall Thanks for the answer. but actually I'm not saying that mysql is doing wrong, I just want to know HOW can I group by codigo and then get 10 random rows of the grouped ones. Hope I explain me. Thanks again. Angel Behar wrote: Hi !!! I'm running 3.23.28-gamma under windows NT, but I have the following problem. I need to query some data and gruop by some field (codigo in this case) so I run the following query : SELECT codigo from ropa where cve_depto='3' AND cve_clase='06' AND activo = 'A' AND p_credito 0 GROUP BY codigo; ++ | codigo | ++ | 319066 | ++ 1 row in set (0.43 sec) the results are as I expected BUT I need to GROUP BY RAND() too and LIMIT to 10 rows : SELECT codigo from ropa where cve_depto='3' AND cve_clase='06' AND activo = 'A' AND p_credito 0 GROUP BY codigo, RAND() LIMIT 10; ++ | codigo | ++ | 319066 | | 319066 | | 319066 | ++ 3 rows in set (0.30 sec) Actually I try switching codigo, RAND() or viceversa but doesn't work either. As you can see once I use the RAND() option doesn't group codigo, I need this because most of the articles are more than 10 but I really need to solve this problem. Hope anybody can help me. Thanks in advance. Angel Each of those 3 lines have a different rand(), so it is doing it right. - 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 - 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 Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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 problem.
Angel Behar wrote: Hi !!! I'm running 3.23.28-gamma under windows NT, but I have the following problem. I need to query some data and gruop by some field (codigo in this case) so I run the following query : SELECT codigo from ropa where cve_depto='3' AND cve_clase='06' AND activo = 'A' AND p_credito 0 GROUP BY codigo; ++ | codigo | ++ | 319066 | ++ 1 row in set (0.43 sec) the results are as I expected BUT I need to GROUP BY RAND() too and LIMIT to 10 rows : SELECT codigo from ropa where cve_depto='3' AND cve_clase='06' AND activo = 'A' AND p_credito 0 GROUP BY codigo, RAND() LIMIT 10; ++ | codigo | ++ | 319066 | | 319066 | | 319066 | ++ 3 rows in set (0.30 sec) Actually I try switching codigo, RAND() or viceversa but doesn't work either. As you can see once I use the RAND() option doesn't group codigo, I need this because most of the articles are more than 10 but I really need to solve this problem. Hope anybody can help me. Thanks in advance. Angel Each of those 3 lines have a different rand(), so it is doing it right. - 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
