Re: Group by question
On 2011-1-16 20:22, Jørn Dahl-Stamnes wrote: Hello, I got a table that store information about which photo-albums that a client is viewing. I want to get the N last visited albums and use the query: mysql select album_id, updated_at, created_at from album_stats order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 51 | 2011-01-16 13:03:04 | 2011-01-16 13:03:04 | | 10 | 2011-01-16 12:20:39 | 2011-01-16 12:20:39 | |2 | 2011-01-16 12:20:35 | 2011-01-16 12:20:35 | | 81 | 2011-01-16 12:20:34 | 2011-01-16 12:20:34 | | 97 | 2011-01-16 11:25:03 | 2011-01-16 11:19:05 | | 81 | 2011-01-16 11:19:04 | 2011-01-16 11:19:04 | |2 | 2011-01-16 11:19:02 | 2011-01-16 11:19:02 | | 10 | 2011-01-16 11:18:58 | 2011-01-16 11:13:04 | +--+-+-+ 8 rows in set (0.09 sec) The problem is that album_id 81 is occuring two times in the list. So I thought I should add a group by in the query: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 278 | 2011-01-13 14:02:50 | 2011-01-13 14:02:00 | | 281 | 2011-01-11 16:41:16 | 2011-01-11 16:35:41 | | 276 | 2010-12-15 14:42:57 | 2010-12-15 14:42:57 | | 275 | 2010-12-15 14:42:48 | 2010-12-15 14:42:48 | | 269 | 2010-09-11 14:09:10 | 2010-09-11 14:09:10 | | 271 | 2010-09-11 14:02:27 | 2010-09-11 14:02:27 | | 273 | 2010-09-11 13:59:06 | 2010-09-11 13:59:06 | | 270 | 2010-09-11 13:57:25 | 2010-09-11 13:57:25 | +--+-+-+ 8 rows in set (0.23 sec) But the result is not what I expected. What have I missed? perhaps i think first you need to retrieve the max(updated_at) group by album_id sets select album_id, updated_at, created_at from album_stats where updated_at in (select max(updated_at) from album_stats group by album_id) limit 8; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Group by question
On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; I believe that your problem is that the group by happens before the order by. Since you're grouping, the updated_at column is not deterministic. If there are multiple rows per album_id, any one of those rows could provide the updated_at column that you're then using to order by. What you probably want is to select (and order by) the max(updated_at). Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Group by question
On Monday 17 January 2011 09:53, Steve Meyers wrote: On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; I believe that your problem is that the group by happens before the order by. Since you're grouping, the updated_at column is not deterministic. If there are multiple rows per album_id, any one of those rows could provide the updated_at column that you're then using to order by. What you probably want is to select (and order by) the max(updated_at). moving the group by to after order by will result in an error: mysql select album_id, updated_at, created_at from album_stats order by updated_at group by album_id desc limit 8; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by album_id desc limit 8' at line 1 Beside, since I'm using Propel and Creole for ORM and database abstraction, I would never be able to change the order of them. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Group by question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 He meant the execution order, please use the agregation function as suggested. On 11-01-17 05:03, Jørn Dahl-Stamnes wrote: On Monday 17 January 2011 09:53, Steve Meyers wrote: On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; I believe that your problem is that the group by happens before the order by. Since you're grouping, the updated_at column is not deterministic. If there are multiple rows per album_id, any one of those rows could provide the updated_at column that you're then using to order by. What you probably want is to select (and order by) the max(updated_at). moving the group by to after order by will result in an error: mysql select album_id, updated_at, created_at from album_stats order by updated_at group by album_id desc limit 8; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by album_id desc limit 8' at line 1 Beside, since I'm using Propel and Creole for ORM and database abstraction, I would never be able to change the order of them. -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.14 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQEcBAEBAgAGBQJNNHQIAAoJENgwSj9ZOOwrR9UH/RDLojUwXYB1a+fcS0kuwzFW AZ/VdwknydB5ENkt7+MmWgHdVcPwrChE+nY2bpRI9LYp8ehUNwaeC2uV/ToWXFti ZTnVdnFDanHy20UOF3a1X7UXW89/zTy/B06X7NP1NqmIGnAahPK6VBuIx1OP/oGZ +es+m9BIYnuc8JzfRo5YSQuydfWIJ87ygrkodhM/C2VPBWDMwpEX/wuxgW/x+ukM RXKaxHrHOrWc1hWLFp3P+QI+J7VNP1fh6Rxw1Q91latJkY4I3hbN9nEsXeHlD2l2 f5ZNn4LwPPNC++XpGaDqQmA0W2Sua9lHUhtVsxsJt5kIigAwNJ5GnaayY/p9apk= =7MLg -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Group by question
Hello, I got a table that store information about which photo-albums that a client is viewing. I want to get the N last visited albums and use the query: mysql select album_id, updated_at, created_at from album_stats order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 51 | 2011-01-16 13:03:04 | 2011-01-16 13:03:04 | | 10 | 2011-01-16 12:20:39 | 2011-01-16 12:20:39 | |2 | 2011-01-16 12:20:35 | 2011-01-16 12:20:35 | | 81 | 2011-01-16 12:20:34 | 2011-01-16 12:20:34 | | 97 | 2011-01-16 11:25:03 | 2011-01-16 11:19:05 | | 81 | 2011-01-16 11:19:04 | 2011-01-16 11:19:04 | |2 | 2011-01-16 11:19:02 | 2011-01-16 11:19:02 | | 10 | 2011-01-16 11:18:58 | 2011-01-16 11:13:04 | +--+-+-+ 8 rows in set (0.09 sec) The problem is that album_id 81 is occuring two times in the list. So I thought I should add a group by in the query: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 278 | 2011-01-13 14:02:50 | 2011-01-13 14:02:00 | | 281 | 2011-01-11 16:41:16 | 2011-01-11 16:35:41 | | 276 | 2010-12-15 14:42:57 | 2010-12-15 14:42:57 | | 275 | 2010-12-15 14:42:48 | 2010-12-15 14:42:48 | | 269 | 2010-09-11 14:09:10 | 2010-09-11 14:09:10 | | 271 | 2010-09-11 14:02:27 | 2010-09-11 14:02:27 | | 273 | 2010-09-11 13:59:06 | 2010-09-11 13:59:06 | | 270 | 2010-09-11 13:57:25 | 2010-09-11 13:57:25 | +--+-+-+ 8 rows in set (0.23 sec) But the result is not what I expected. What have I missed? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select w/ group by question
I'm having trouble formulating a query to gather the following data. I can do this via a script, but now it is more or less just bothering me if there is an easy/efficient way to gather the following data from a single query. Example Tables Products Type Cost Vendor_id -- --- apple11 apple32 apple73 pear 2 1 pear 42 pear 23 Vendors Vendor_id Vendor_name 1 Walmart 2 Target 3 Kmart I would like to obtain the least cost of each product type and its associated vendor. So...I would like to see a result similiar to the following: Type CostVendor_id Vendor_name apple 1 1 Walmart pear 2 1 Walmart (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not really concerned with which vendor is chosen in the result set here.) If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from products a join vendors b on a.vendor_id = b.vendor_id group by a.type, a.vendor_id, b.vendor_name all rows are returned because the type/vendor_id/vendor_name are unique amongst each row. If you remove the vendor_id and vendor_name from the group by, you get a single row with the lowest cost for each product, but the vendor_id's and vendor_name's are incorrect because you are not grouping by them. Is there a way to do this from a single query. I know I can concat things together and imbed a select in my where clause to get the result I want, but this is horribly inefficient. My real tables have somewhere around 30 million rows in them. Thanks Scott
Re: Select w/ group by question
Scott, I would like to obtain the least cost of each product type and its associated vendor. See Within-group aggregates at http://www.artfulsoftware.com/infotree/queries.php. PB - On 7/14/2010 9:25 AM, Scott Mullen wrote: I'm having trouble formulating a query to gather the following data. I can do this via a script, but now it is more or less just bothering me if there is an easy/efficient way to gather the following data from a single query. Example Tables Products Type Cost Vendor_id -- --- apple11 apple32 apple73 pear 2 1 pear 42 pear 23 Vendors Vendor_id Vendor_name 1 Walmart 2 Target 3 Kmart I would like to obtain the least cost of each product type and its associated vendor. So...I would like to see a result similiar to the following: Type CostVendor_id Vendor_name apple 1 1 Walmart pear 2 1 Walmart (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not really concerned with which vendor is chosen in the result set here.) If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from products a join vendors b on a.vendor_id = b.vendor_id group by a.type, a.vendor_id, b.vendor_name all rows are returned because the type/vendor_id/vendor_name are unique amongst each row. If you remove the vendor_id and vendor_name from the group by, you get a single row with the lowest cost for each product, but the vendor_id's and vendor_name's are incorrect because you are not grouping by them. Is there a way to do this from a single query. I know I can concat things together and imbed a select in my where clause to get the result I want, but this is horribly inefficient. My real tables have somewhere around 30 million rows in them. Thanks Scott No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.441 / Virus Database: 271.1.1/3004 - Release Date: 07/14/10 06:36:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select w/ group by question
On Wednesday, July 14, 2010 09:25:22 am Scott Mullen wrote: I'm having trouble formulating a query to gather the following data. I can do this via a script, but now it is more or less just bothering me if there is an easy/efficient way to gather the following data from a single query. Example Tables Products Type Cost Vendor_id -- --- apple11 apple32 apple73 pear 2 1 pear 42 pear 23 Vendors Vendor_id Vendor_name 1 Walmart 2 Target 3 Kmart I would like to obtain the least cost of each product type and its associated vendor. So...I would like to see a result similiar to the following: Type CostVendor_id Vendor_name apple 1 1 Walmart pear 2 1 Walmart (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not really concerned with which vendor is chosen in the result set here.) Try this: select name, product_type, min(cost) from vendors join products on vendors.id = products.vendor_id group by product_type; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Select w/ group by question
Date: Wed, 14 Jul 2010 10:25:22 -0400 Subject: Select w/ group by question From: smulle...@gmail.com To: mysql@lists.mysql.com I'm having trouble formulating a query to gather the following data. I can do this via a script, but now it is more or less just bothering me if there is an easy/efficient way to gather the following data from a single query. Example Tables Products Type Cost Vendor_id -- --- apple 1 1 apple 3 2 apple 7 3 pear 2 1 pear 4 2 pear 2 3 Vendors Vendor_id Vendor_name 1 Walmart 2 Target 3 Kmart I would like to obtain the least cost of each product type and its associated vendor. So...I would like to see a result similiar to the following: Type Cost Vendor_id Vendor_name apple 1 1 Walmart pear 2 1 Walmart (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not really concerned with which vendor is chosen in the result set here.) If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from products a join vendors b on a.vendor_id = b.vendor_id group by a.type,a.vendor_id, b.vendor_name all rows are returned because the type/vendor_id/vendor_name are unique amongst each row. If you remove the vendor_id and vendor_name from the group by, select a.type, min(a.cost), a.vendor_id,b.vendor_name from products a join vendors b on a.vendor_id = b.vendor_id group by a.type; you get a single row with the lowest cost for each product, but the vendor_id's and vendor_name's are incorrect because you are not grouping by them. Is there a way to do this from a single query. I know I can concat things together and imbed a select in my where clause to get the result I want, but this is horribly inefficient. My real tables have somewhere around 30 million rows in them. MGput ascending indexes on vendor_id and vendor_name columns MGreorg the tables so the rows will be in vendor_name (within vendor_id) order MGreselect MGselect a.type, min(a.cost), a.vendor_id,b.vendor_name from MGproducts a join vendors b MGon a.vendor_id = b.vendor_id MGorder by a.type; Thanks Scott _ The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. http://www.windowslive.com/campaign/thenewbusy?tile=multicalendarocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5
Re: Select w/ group by question
Peter Thanks for the link. I've never run across this page before, but it has tons of useful informationas well as several answers on how to implement what I was trying to do. Ended up going with a solution similar to this example (from the page you referenced): SELECT item, SUBSTR( MIN( CONCAT( LPAD(price,6,0),supplier) ), 7) AS MinSupplier, LEFT( MIN( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MinPrice, SUBSTR( MAX( CONCAT( LPAD(price,6,0),supplier) ), 7) AS MaxSupplier, LEFT( MAX( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MaxPrice FROM products GROUP BY item; Pretty straight forward and does not require another join back to the same table with 30+ million rows. Thanks Scott On Wed, Jul 14, 2010 at 10:35 AM, Peter Brawley peter.braw...@earthlink.net wrote: Scott, I would like to obtain the least cost of each product type and its associated vendor. See Within-group aggregates at http://www.artfulsoftware.com/infotree/queries.php. PB - On 7/14/2010 9:25 AM, Scott Mullen wrote: I'm having trouble formulating a query to gather the following data. I can do this via a script, but now it is more or less just bothering me if there is an easy/efficient way to gather the following data from a single query. Example Tables Products Type Cost Vendor_id -- --- apple11 apple32 apple73 pear 2 1 pear 42 pear 23 Vendors Vendor_id Vendor_name 1 Walmart 2 Target 3 Kmart I would like to obtain the least cost of each product type and its associated vendor. So...I would like to see a result similiar to the following: Type CostVendor_id Vendor_name apple 1 1 Walmart pear 2 1 Walmart (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not really concerned with which vendor is chosen in the result set here.) If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from products a join vendors b on a.vendor_id = b.vendor_id group by a.type, a.vendor_id, b.vendor_name all rows are returned because the type/vendor_id/vendor_name are unique amongst each row. If you remove the vendor_id and vendor_name from the group by, you get a single row with the lowest cost for each product, but the vendor_id's and vendor_name's are incorrect because you are not grouping by them. Is there a way to do this from a single query. I know I can concat things together and imbed a select in my where clause to get the result I want, but this is horribly inefficient. My real tables have somewhere around 30 million rows in them. Thanks Scott No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.441 / Virus Database: 271.1.1/3004 - Release Date: 07/14/10 06:36:00
Stupid GROUP BY question
It must be too late in the week… Suppose table Cust has one field, CustID. Suppose table Notes has four fields: NoteID (unique), CustID, NoteTime, and NoteText. A customer can have zero or more notes. Now here’s the seemingly simple problem that I’m trying to solve: I want to find the newest note (if any) for each customer. If all I want is the date, then I can do SELECT Cust.CustID, MAX(Notes.NoteTime) FROM Cust LEFT JOIN Notes ON Cust.CustID = Notes.Cust_ID GROUP BY Cust.CustID; That will work just fine, but now I also want the NoteText associated with the newest note. Obviously I can’t use MAX(NoteText). I could do this using a temporary table, but it seems like there should be another way. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com
Re: Stupid GROUP BY question
Jerry, I want to find the newest note (if any) for each customer. See Within-group aggregates at http://www.artfulsoftware.com/infotree/queries.php http://www.artfulsoftware.com/queries.php PB - Jerry Schwartz wrote: It must be too late in the week... Suppose table Cust has one field, CustID. Suppose table Notes has four fields: NoteID (unique), CustID, NoteTime, and NoteText. A customer can have zero or more notes. Now here's the seemingly simple problem that I'm trying to solve: I want to find the newest note (if any) for each customer. If all I want is the date, then I can do SELECT Cust.CustID, MAX(Notes.NoteTime) FROM Cust LEFT JOIN Notes ON Cust.CustID = Notes.Cust_ID GROUP BY Cust.CustID; That will work just fine, but now I also want the NoteText associated with the newest note. Obviously I can't use MAX(NoteText). I could do this using a temporary table, but it seems like there should be another way. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.112/2394 - Release Date: 09/25/09 05:51:00
RE: Stupid GROUP BY question
Commonly refered to as a groupwise max http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html http://jan.kneschke.de/projects/mysql/groupwise-max/ Regards, Gavin Towey -Original Message- From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com] Sent: Friday, September 25, 2009 1:28 PM To: mysql@lists.mysql.com Subject: Stupid GROUP BY question It must be too late in the week… Suppose table Cust has one field, CustID. Suppose table Notes has four fields: NoteID (unique), CustID, NoteTime, and NoteText. A customer can have zero or more notes. Now here’s the seemingly simple problem that I’m trying to solve: I want to find the newest note (if any) for each customer. If all I want is the date, then I can do SELECT Cust.CustID, MAX(Notes.NoteTime) FROM Cust LEFT JOIN Notes ON Cust.CustID = Notes.Cust_ID GROUP BY Cust.CustID; That will work just fine, but now I also want the NoteText associated with the newest note. Obviously I can’t use MAX(NoteText). I could do this using a temporary table, but it seems like there should be another way. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Group by question
A question on grouping I've never been able to solve...
create table j (proj char(3), id int, score double,cpid char(32),team
char(10));
insert into j values('aaa',1,100,'a','team1');
insert into j values('bbb',2,200,'a','team1');
insert into j values('ccc',3,300,'a','team2');
insert into j values('aaa',4,100,'b','team2');
insert into j values('bbb',5,300,'b','team1');
insert into j values('ccc',6,400,'b','team1');
insert into j values('aaa',7,101,'c','team1');
insert into j values('bbb',8,302,'c','team2');
insert into j values('ccc',9,503,'c','team2');
mysql select * from j;
+--+--+---+---+---+
| proj | id | score | cpid | team |
+--+--+---+---+---+
| aaa |1 | 100 | a | team1 |
| bbb |2 | 200 | a | team1 |
| ccc |3 | 300 | a | team2 |
| aaa |4 | 100 | b | team2 |
| bbb |5 | 300 | b | team1 |
| ccc |6 | 400 | b | team1 |
| aaa |7 | 101 | c | team1 |
| bbb |8 | 302 | c | team2 |
| ccc |9 | 503 | c | team2 |
+--+--+---+---+---+
9 rows in set (0.00 sec)
mysql select cpid,sum(score),team from j group by cpid;
+---++---+
| cpid | sum(score) | team |
+---++---+
| a |600 | team1 |
| b |800 | team2 |
| c |906 | team1 |
+---++---+
3 rows in set (0.00 sec)
Using MAX or MIN on the team gives different but not necessarily closer
results.
mysql select cpid,sum(score),max(team) from j group by cpid;
+---++---+
| cpid | sum(score) | max(team) |
+---++---+
| a |600 | team2 |
| b |800 | team2 |
| c |906 | team2 |
+---++---+
3 rows in set (0.00 sec)
mysql select cpid,sum(score),min(team) from j group by cpid;
+---++---+
| cpid | sum(score) | min(team) |
+---++---+
| a |600 | team1 |
| b |800 | team1 |
| c |906 | team1 |
+---++---+
3 rows in set (0.00 sec)
Given that for cpid = 'bbb', they have 2 rows where it is team1,
and only 1 with team2 but the original query gives team2 and rightly so as
it just uses the first row in mysql's slightly illegal (but useful!) use of
allowing other columns in the query but not in the group by.
The question is, is there any way to modify this query so that it would
return the team having the most entries?
Theoretical what I would like:
| cpid | sum(score) | team |
+---++---+
| a |600 | team1 |
| b |800 | team1 |
| c |906 | team2 |
If not, is there an easy way to have another column, say mostteam char(10)
and run an update statement on the whole table which would put the correct
value in?
Regards
Phil
--
Distributed Computing stats
http://stats.free-dc.org
Re: Group by question
Phil
is there any way to modify this query so that it would
return the team having the most entries?
See Within-group aggregates at http://www.artfulsoftware.com/queries.php
PB
-
Phil wrote:
A question on grouping I've never been able to solve...
create table j (proj char(3), id int, score double,cpid char(32),team
char(10));
insert into j values('aaa',1,100,'a','team1');
insert into j values('bbb',2,200,'a','team1');
insert into j values('ccc',3,300,'a','team2');
insert into j values('aaa',4,100,'b','team2');
insert into j values('bbb',5,300,'b','team1');
insert into j values('ccc',6,400,'b','team1');
insert into j values('aaa',7,101,'c','team1');
insert into j values('bbb',8,302,'c','team2');
insert into j values('ccc',9,503,'c','team2');
mysql select * from j;
+--+--+---+---+---+
| proj | id | score | cpid | team |
+--+--+---+---+---+
| aaa |1 | 100 | a | team1 |
| bbb |2 | 200 | a | team1 |
| ccc |3 | 300 | a | team2 |
| aaa |4 | 100 | b | team2 |
| bbb |5 | 300 | b | team1 |
| ccc |6 | 400 | b | team1 |
| aaa |7 | 101 | c | team1 |
| bbb |8 | 302 | c | team2 |
| ccc |9 | 503 | c | team2 |
+--+--+---+---+---+
9 rows in set (0.00 sec)
mysql select cpid,sum(score),team from j group by cpid;
+---++---+
| cpid | sum(score) | team |
+---++---+
| a |600 | team1 |
| b |800 | team2 |
| c |906 | team1 |
+---++---+
3 rows in set (0.00 sec)
Using MAX or MIN on the team gives different but not necessarily closer
results.
mysql select cpid,sum(score),max(team) from j group by cpid;
+---++---+
| cpid | sum(score) | max(team) |
+---++---+
| a |600 | team2 |
| b |800 | team2 |
| c |906 | team2 |
+---++---+
3 rows in set (0.00 sec)
mysql select cpid,sum(score),min(team) from j group by cpid;
+---++---+
| cpid | sum(score) | min(team) |
+---++---+
| a |600 | team1 |
| b |800 | team1 |
| c |906 | team1 |
+---++---+
3 rows in set (0.00 sec)
Given that for cpid = 'bbb', they have 2 rows where it is team1,
and only 1 with team2 but the original query gives team2 and rightly so as
it just uses the first row in mysql's slightly illegal (but useful!) use of
allowing other columns in the query but not in the group by.
The question is, is there any way to modify this query so that it would
return the team having the most entries?
Theoretical what I would like:
| cpid | sum(score) | team |
+---++---+
| a |600 | team1 |
| b |800 | team1 |
| c |906 | team2 |
If not, is there an easy way to have another column, say mostteam char(10)
and run an update statement on the whole table which would put the correct
value in?
Regards
Phil
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.176 / Virus Database: 270.10.4/1880 - Release Date: 1/7/2009 8:49 AM
Re: Group by question
Hello,
I think following query would help you
For Ascending
select cpid,sum(score),team from j group by cpid order by sum(score)
For Descending
select cpid,sum(score),team from j group by cpid order by sum(score) desc
Thanks!
2009/1/7 Phil freedc@gmail.com
A question on grouping I've never been able to solve...
create table j (proj char(3), id int, score double,cpid char(32),team
char(10));
insert into j values('aaa',1,100,'a','team1');
insert into j values('bbb',2,200,'a','team1');
insert into j values('ccc',3,300,'a','team2');
insert into j values('aaa',4,100,'b','team2');
insert into j values('bbb',5,300,'b','team1');
insert into j values('ccc',6,400,'b','team1');
insert into j values('aaa',7,101,'c','team1');
insert into j values('bbb',8,302,'c','team2');
insert into j values('ccc',9,503,'c','team2');
mysql select * from j;
+--+--+---+---+---+
| proj | id | score | cpid | team |
+--+--+---+---+---+
| aaa |1 | 100 | a | team1 |
| bbb |2 | 200 | a | team1 |
| ccc |3 | 300 | a | team2 |
| aaa |4 | 100 | b | team2 |
| bbb |5 | 300 | b | team1 |
| ccc |6 | 400 | b | team1 |
| aaa |7 | 101 | c | team1 |
| bbb |8 | 302 | c | team2 |
| ccc |9 | 503 | c | team2 |
+--+--+---+---+---+
9 rows in set (0.00 sec)
mysql select cpid,sum(score),team from j group by cpid;
+---++---+
| cpid | sum(score) | team |
+---++---+
| a |600 | team1 |
| b |800 | team2 |
| c |906 | team1 |
+---++---+
3 rows in set (0.00 sec)
Using MAX or MIN on the team gives different but not necessarily closer
results.
mysql select cpid,sum(score),max(team) from j group by cpid;
+---++---+
| cpid | sum(score) | max(team) |
+---++---+
| a |600 | team2 |
| b |800 | team2 |
| c |906 | team2 |
+---++---+
3 rows in set (0.00 sec)
mysql select cpid,sum(score),min(team) from j group by cpid;
+---++---+
| cpid | sum(score) | min(team) |
+---++---+
| a |600 | team1 |
| b |800 | team1 |
| c |906 | team1 |
+---++---+
3 rows in set (0.00 sec)
Given that for cpid = 'bbb', they have 2 rows where it is
team1,
and only 1 with team2 but the original query gives team2 and rightly so as
it just uses the first row in mysql's slightly illegal (but useful!) use of
allowing other columns in the query but not in the group by.
The question is, is there any way to modify this query so that it would
return the team having the most entries?
Theoretical what I would like:
| cpid | sum(score) | team |
+---++---+
| a |600 | team1 |
| b |800 | team1 |
| c |906 | team2 |
If not, is there an easy way to have another column, say mostteam char(10)
and run an update statement on the whole table which would put the correct
value in?
Regards
Phil
--
Distributed Computing stats
http://stats.free-dc.org
Re: MAX() and GROUP BY question
On 11/10/06, [EMAIL PROTECTED] wrote: Hello everybody Can you explain me please how to get the entire row where ID is maximum per given status_id Mysql 4.0.xx Have you checked the manual? There is an entire page specifically about the group-wise maximum. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MAX() and GROUP BY question
How about select ID, X, Y, Z from USERS order by ID desc limit 1 Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 10, 2006 5:47 PM To: mysql@lists.mysql.com Subject: MAX() and GROUP BY question Hello everybody Can you explain me please how to get the entire row where ID is maximum per given status_id Mysql 4.0.xx for example: id status_idnamedate 6 3 name0 date0 5 1 name1 date1 7 4 name3 date3 10 3 name2 date2 If I execute SELECT MAX(id), status, name, date FROM table_name WHERE status=3 GROUP BY staus_id or SELECT MAX(id), status, name, date GROUP BY staus_id only id and status_id will be returned correctly, while name and date can well be from another row for example: 10 3 name0 date0 Thanks a lot for any suggestions Yannis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAX() and GROUP BY question
No, this won't work I actually have a table of support ticket records record_id ticket_id date name status department_id and other columns I must select an ordered list of ticket_id where each row is the most recent record of gived ticket: max(record_id) for given ticket_id I currently solved this using two queries: First will select a list of max record_id's: SELECT MAX(record_id) GROUP BY ticket_id Then using API I create a comma separated list of record_id's. Then I select the latest records of each ticket: SELECT * WHERE record_id IN () I just wonder if there is more optimised solution Thanks a lot - Original Message From: Tim Lucia [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 11, 2006 3:56:49 PM Subject: RE: MAX() and GROUP BY question How about select ID, X, Y, Z from USERS order by ID desc limit 1 Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 10, 2006 5:47 PM To: mysql@lists.mysql.com Subject: MAX() and GROUP BY question Hello everybody Can you explain me please how to get the entire row where ID is maximum per given status_id Mysql 4.0.xx for example: id status_idnamedate 6 3 name0 date0 5 1 name1 date1 7 4 name3 date3 10 3 name2 date2 If I execute SELECT MAX(id), status, name, date FROM table_name WHERE status=3 GROUP BY staus_id or SELECT MAX(id), status, name, date GROUP BY staus_id only id and status_id will be returned correctly, while name and date can well be from another row for example: 10 3 name0 date0 Thanks a lot for any suggestions Yannis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MAX() and GROUP BY question
Hello everybody Can you explain me please how to get the entire row where ID is maximum per given status_id Mysql 4.0.xx for example: id status_idnamedate 6 3 name0 date0 5 1 name1 date1 7 4 name3 date3 10 3 name2 date2 If I execute SELECT MAX(id), status, name, date FROM table_name WHERE status=3 GROUP BY staus_id or SELECT MAX(id), status, name, date GROUP BY staus_id only id and status_id will be returned correctly, while name and date can well be from another row for example: 10 3 name0 date0 Thanks a lot for any suggestions Yannis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By question
Hello , Another one alternate solution without join SELECT id,count(email) as No ,email FROM email GROUP BY email having count(email)1 Thanks Visolve DB Team. - Original Message - From: Peter Brawley To: Chris W Cc: MYSQL General List Sent: Thursday, August 31, 2006 3:02 AM Subject: Re: Group By question Chris, I would like to query all rows that have more than one person with the same email address. select id,count(emailaddr) as howmany from tbl t1 join tbl t2 using(emailaddr) group by id having howmany1; PB - Chris W wrote: I have a table of people with one of the fields being the email address. I would like to query all rows that have more than one person with the same email address. For example if the data were like this... A [EMAIL PROTECTED] B [EMAIL PROTECTED] C [EMAIL PROTECTED] D [EMAIL PROTECTED] E [EMAIL PROTECTED] F [EMAIL PROTECTED] The query would return row A, D, B, and E, in that order. It would not return C or F -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.7/433 - Release Date: 8/30/2006 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By question
select * from t where emailaddress in (select emailaddress from t group by emailaddress having count(*) 1) order by emailaddress; Thanks, Michael -Original Message- From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wed, 30 Aug 2006 5:17 PM Subject: Group By question I have a table of people with one of the fields being the email address. I would like to query all rows that have more than one person with the same email address. For example if the data were like this... A [EMAIL PROTECTED] B [EMAIL PROTECTED] C [EMAIL PROTECTED] D [EMAIL PROTECTED] E [EMAIL PROTECTED] F [EMAIL PROTECTED] The query would return row A, D, B, and E, in that order. It would not return C or F -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Check Out the new free AIM(R) Mail -- 2 GB of storage and industry-leading spam and email virus protection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group By question
I have a table of people with one of the fields being the email address. I would like to query all rows that have more than one person with the same email address. For example if the data were like this... A [EMAIL PROTECTED] B [EMAIL PROTECTED] C [EMAIL PROTECTED] D [EMAIL PROTECTED] E [EMAIL PROTECTED] F [EMAIL PROTECTED] The query would return row A, D, B, and E, in that order. It would not return C or F -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By question
Chris, I would like to query all rows that have more than one person with the same email address. select id,count(emailaddr) as howmany from tbl t1 join tbl t2 using(emailaddr) group by id having howmany1; PB - Chris W wrote: I have a table of people with one of the fields being the email address. I would like to query all rows that have more than one person with the same email address. For example if the data were like this... A [EMAIL PROTECTED] B [EMAIL PROTECTED] C [EMAIL PROTECTED] D [EMAIL PROTECTED] E [EMAIL PROTECTED] F [EMAIL PROTECTED] The query would return row A, D, B, and E, in that order. It would not return C or F No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.7/433 - Release Date: 8/30/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY question
Sorry about the re-post, all! Hello all, I just inherited an application that has 2 tables under consideration, events and attribute_master. They are linked on (events.eventID = attribute_master.id AND attribute_master.tableis = 'events'). In other words, attribute_master.id is kinda like a foreign key to events.eventID, but only where attribute_master.tableis = 'events'. I have ommited some columns from 'events' that I am fairly certain are NOT germane to my question: CREATE TABLE attribute_master ( tableis varchar(128) NOT NULL default '', id int(12) NOT NULL default '0', attributeID int(8) NOT NULL default '0', PRIMARY KEY (tableis,id,attributeID) ) TYPE=MyISAM; CREATE TABLE events ( eventID mediumint(9) NOT NULL auto_increment, eventReview text NOT NULL, status tinyint(1) NOT NULL default '0', modlast int(8) NOT NULL default '0', PRIMARY KEY (eventID) ) TYPE=MyISAM Now, an intersting query: SELECT events.eventID AS id, attribute_master.attributeID AS attrib_id FROM events, attribute_master WHERE events.status='8' AND events.eventReview!='' AND events.modlast 1146470445 AND events.eventID = attribute_master.id AND attribute_master.tableis = 'events' GROUP BY attribute_master.id So here's my question(s): Given this query, how can on GROUP BY operate on a column that doesn't appear in the SELECT list? Secondly, I thought that every column listed in the SELECT clause would have to be in an aggregate function or mentioned in the GROUP BY...how am I wrong? -- Wellington -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By Question
In article [EMAIL PROTECTED], Fan, Wellington [EMAIL PROTECTED] writes: Hello Listfolk, I have a table with a 'category_fk' column and a 'status' column. 'Status' has but a tiny handful of known values, kinda like an enum. I'd like to form a query that would give me results like: category_fk | status=1 | status=2 | status=3 | toys | 23|45|0 | games | 12|0 |0 | books | 5 |1 |3 | Where the non-fk columns represent the counts of records with that category_fk with a certain 'status' I've got something close: SELECT category_fk, count(*) as n, status FROM myTable GROUP BY category_fk, status But this gives me a record for each category_fk/status. SELECT category_fk, sum(case status when 1 then 1 else 0 end) AS 'status=1', sum(case status when 2 then 1 else 0 end) AS 'status=2', sum(case status when 3 then 1 else 0 end) AS 'status=3' FROM myTable GROUP BY category_fk; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Group By Question
Perfect! Thank you. -Original Message- From: Harald Fuchs [mailto:[EMAIL PROTECTED] Subject: Re: Group By Question SELECT category_fk, sum(case status when 1 then 1 else 0 end) AS 'status=1', sum(case status when 2 then 1 else 0 end) AS 'status=2', sum(case status when 3 then 1 else 0 end) AS 'status=3' FROM myTable GROUP BY category_fk; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group By Question
Hello Listfolk, I have a table with a 'category_fk' column and a 'status' column. 'Status' has but a tiny handful of known values, kinda like an enum. I'd like to form a query that would give me results like: category_fk | status=1 | status=2 | status=3 | toys | 23|45|0 | games | 12|0 |0 | books | 5 |1 |3 | Where the non-fk columns represent the counts of records with that category_fk with a certain 'status' I've got something close: SELECT category_fk, count(*) as n, status FROM myTable GROUP BY category_fk, status But this gives me a record for each category_fk/status. TIA! -- WF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select all and group by question
Hi, I'm using MySQL 4.0.18 on Win2k Server. I have a table similar to the following: col1col2col3 A 1 this A 2 is A 3 a B 1 test B 2 table What I would like to get with one query is the following result: col1max(col2) col3 -- A 3 a B 2 table I've searched around, but haven't been able to come up with anything. This query doesn't give me the correct results: select col1, max(col2), col3 from table group by col1 Any suggestions? Thanks in advance, Matt
Re: select all and group by question
Without using a subquery, you have to do it in two steps. First you need to collect the MAX(col2) values for each col1 value: CREATE TEMPORARY TABLE tmpMaxes SELECT col1, max(col2) from test_table GROUP BY col1 Then you want to get the rows where the col2 value is the maximum for each col1 value (as we determined in the last query): SELECT tt.col1, tt.col2, tt.col3 FROM test_table tt INNER JOIN tmpMaxes tm ON tm.col1 = tt.col1 AND tm.col2 = tt.col2 Finally, you should always clean up after yourself: DROP TABLE tmpMaxes Make sense? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 07/28/2004 12:29:26 PM: Hi, I'm using MySQL 4.0.18 on Win2k Server. I have a table similar to the following: col1col2col3 A 1 this A 2 is A 3 a B 1 test B 2 table What I would like to get with one query is the following result: col1max(col2) col3 -- A 3 a B 2 table I've searched around, but haven't been able to come up with anything. This query doesn't give me the correct results: select col1, max(col2), col3 from table group by col1 Any suggestions? Thanks in advance, Matt
Re: GROUP BY question
* Bengt Lindholm I have a table where I need to group the content on a timestamp. Any record that is less than say 5 minutes from any other record needs to be grouped with that other record. ID timestamp 1 2004-02-02 12:00:00 2 2004-02-02 12:00:05 3 2004-02-02 12:05:20 4 2004-02-02 12:15:00 5 2004-02-02 12:17:15 6 2004-02-02 12:21:20 With this recordset I would get: 1 2 should be grouped since there's less than 5 minutes between the records 3 will be in a group of its own 4, 5 6 is a third group since any record in the group has less than 5 minutes to another record in the group Is this possible to do in MySQL, and how is it accomplished? You could use mysql user variables: mysql set @a:=NULL,@b:=0; Query OK, 0 rows affected (0.00 sec) mysql select if(clicktime-interval 5 minute[EMAIL PROTECTED],@b:[EMAIL PROTECTED],@b) x, - @a:=clicktime from stats where clicktime between - 2003-11-24 02:00:00 and 2003-11-24 02:59:59 - order by clicktime; +--+-+ | x| @a:=clicktime | +--+-+ |0 | 2003-11-24 02:09:57 | |0 | 2003-11-24 02:10:05 | |0 | 2003-11-24 02:10:09 | |0 | 2003-11-24 02:10:22 | |0 | 2003-11-24 02:10:24 | : |0 | 2003-11-24 02:41:42 | |0 | 2003-11-24 02:41:43 | |0 | 2003-11-24 02:43:12 | |1 | 2003-11-24 02:49:19 | |1 | 2003-11-24 02:50:46 | : |1 | 2003-11-24 02:56:28 | |1 | 2003-11-24 02:56:41 | |1 | 2003-11-24 02:56:50 | +--+-+ 136 rows in set (0.01 sec) As you can see, I only get two groups with my test data, and the result seems to be correct, but when I apply a GROUP BY and a COUNT() I get a different result. I get groups of all rows within five minutes of the _first_ row of each group: mysql set @a:=NULL,@b:=0; Query OK, 0 rows affected (0.00 sec) mysql select if(clicktime-interval 5 minute[EMAIL PROTECTED],@b:[EMAIL PROTECTED],@b) x, - @a:=clicktime,count(*) from stats where clicktime between - 2003-11-24 02:00:00 and 2003-11-24 02:59:59 - group by x order by clicktime; +--+-+--+ | x| @a:=clicktime | count(*) | +--+-+--+ |0 | 2003-11-24 02:09:57 | 22 | |2 | 2003-11-24 02:15:17 | 29 | |4 | 2003-11-24 02:20:55 | 17 | |6 | 2003-11-24 02:25:55 | 22 | |8 | 2003-11-24 02:31:12 |4 | | 10 | 2003-11-24 02:36:19 | 21 | | 12 | 2003-11-24 02:41:42 |3 | | 14 | 2003-11-24 02:49:19 | 13 | | 16 | 2003-11-24 02:56:12 |5 | +--+-+--+ 9 rows in set (0.00 sec) This seems to have something to do with how mysql handles GROUP BY queries, the @a variable is not re-assigned for each row. Note that this is a non-standard GROUP BY statement, as a column (clicktime) is used in the field list but not in the GROUP BY clause. Using a group function (MIN() in this case) on the assignment expression makes this a more standard compliant GROUP BY, and it seems to give the result we want: mysql set @a:=NULL,@b:=0; Query OK, 0 rows affected (0.00 sec) mysql select if(clicktime-interval 5 minute[EMAIL PROTECTED],@b:[EMAIL PROTECTED],@b) x, - min(@a:=clicktime),count(*) from stats where clicktime between - 2003-11-24 02:00:00 and 2003-11-24 02:59:59 - group by x order by clicktime; +--+-+--+ | x| min(@a:=clicktime) | count(*) | +--+-+--+ |0 | 2003-11-24 02:09:57 | 118 | |1 | 2003-11-24 02:49:19 | 18 | +--+-+--+ 2 rows in set (0.01 sec) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY question
I have a table where I need to group the content on a timestamp. Any record that is less than say 5 minutes from any other record needs to be grouped with that other record. ID timestamp 1 2004-02-02 12:00:00 2 2004-02-02 12:00:05 3 2004-02-02 12:05:20 4 2004-02-02 12:15:00 5 2004-02-02 12:17:15 6 2004-02-02 12:21:20 With this recordset I would get: 1 2 should be grouped since there's less than 5 minutes between the records 3 will be in a group of its own 4, 5 6 is a third group since any record in the group has less than 5 minutes to another record in the group Is this possible to do in MySQL, and how is it accomplished? Regards, Bengt Lindholm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY question
On 2004-02-10, at 16.21, Brian Power wrote: I'm not sure if it is possible to do with a group by Say you had 1 2004-02-02 12:00:00 2 2004-02-02 12:00:05 3 2004-02-02 12:00:09 4 2004-02-02 12:00:12 this would require 1,2 and 3 in one group and 2,3,4 in another. My understanding is that you cant have the same rec in two groups Thanks Brian, In your example they would all be in the same group. You could say the group delimiter is any gap between records that is 5 minutes or more. So records would be in the same group even if the total timespan for the group is more than 5 minutes, but all gaps between individual records in the group is less than 5 minutes. No record would then be in more than 1 group. Regards, Bengt Lindholm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY question
Bengt Lindholm wrote: In your example they would all be in the same group. You could say the group delimiter is any gap between records that is 5 minutes or more. So records would be in the same group even if the total timespan for the group is more than 5 minutes, but all gaps between individual records in the group is less than 5 minutes. No record would then be in more than 1 group. The sensible approach would be to use a procedural language :-) However, in SQL, you could try the following approach: SELECT a.timestamp, MAX(b.timestamp) FROM a INNER JOIN b ON a.timestamp b.timestamp GROUP BY a.timestamp HAVING a.timestamp - MAX(b.timestamp) '5 minutes' This gives you every last record of a group. Join against itself again, count the number of last records that is smaller then the target row and group by that count. Probably gets nested about 4 levels deep, so I am not going to write it out ;-) Let me stress again that I would use a procedural language and not SQL. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN + GROUP BY question
At 20:21 +0100 12/17/02, Csongor Fagyal wrote: Hi, I have two tables: one holding bids for an auction (table bids) and one holding user data who placed the bids (users). I would like to get the highest bid, the user who placed the bid and the number of bids placed, so I use the following query: SELECT MAX(bids.amount) AS amount, COUNT(bids.itemid) AS bidcount, bids.bidderid, users.username FROM bids LEFT JOIN users ON bids.bidderid=users.userid WHERE bids.itemid = 71580 AND users.userid IS NOT NULL GROUP BY bids.itemid; What I get is not totally OK: amount is OK, COUNT is also OK, but the user (users.username) I get is not the one who placed the highest bid, but someone in the middle. How come? Am I misunderstanding something about GROUP BY on a joined table? No, you're misunderstanding something about GROUP BY. When you group on a column or set of columns, you can select for output those columns, and aggregate (summary) function values on other columns, but you cannot select other columns. You're grouping by itemid, but selecting bidderid and username for output. So you get indeterminate results. THX, - Csongor - 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: JOIN + GROUP BY question
I have two tables: one holding bids for an auction (table bids) and one holding user data who placed the bids (users). I would like to get the highest bid, the user who placed the bid and the number of bids placed, so I use the following query: SELECT MAX(bids.amount) AS amount, COUNT(bids.itemid) AS bidcount, bids.bidderid, users.username FROM bids LEFT JOIN users ON bids.bidderid=users.userid WHERE bids.itemid = 71580 AND users.userid IS NOT NULL GROUP BY bids.itemid; What I get is not totally OK: amount is OK, COUNT is also OK, but the user (users.username) I get is not the one who placed the highest bid, but someone in the middle. How come? Am I misunderstanding something about GROUP BY on a joined table? Well, never mind... MAX() is a group function, obviously this query is totally wrong. - Csongor - 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
JOIN + GROUP BY question
Hi, I have two tables: one holding bids for an auction (table bids) and one holding user data who placed the bids (users). I would like to get the highest bid, the user who placed the bid and the number of bids placed, so I use the following query: SELECT MAX(bids.amount) AS amount, COUNT(bids.itemid) AS bidcount, bids.bidderid, users.username FROM bids LEFT JOIN users ON bids.bidderid=users.userid WHERE bids.itemid = 71580 AND users.userid IS NOT NULL GROUP BY bids.itemid; What I get is not totally OK: amount is OK, COUNT is also OK, but the user (users.username) I get is not the one who placed the highest bid, but someone in the middle. How come? Am I misunderstanding something about GROUP BY on a joined table? THX, - Csongor - 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
Newbie GROUP-type question
I'm relatively new to the construction of complex queries, and I'm trying to figure one out that I'm sure is common; I wasn't able to find anything especially helpful in the docs. Suppose I have the canonical database of CDs, with one table, which includes fields for title, cost, and date_purchased. I'd like to get a result set that shows the number of CDs purchased, and their sum cost, for each month. Thus, if I bought some CDs that cost exactly $15.00 each month for the last few months, I'll get something like 2001-12 2 30.00 2002-01 3 45.00 2002-02 2 30.00 2002-03 1 15.00 (The exact format isn't too important, I can figure that out once I get the basic query down.) What's the best way to construct this SQL query? Thanks. Jesse Sheidlower - 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: Newbie GROUP-type question
select date_format(datepurch,'%Y-%m') as MONTH, count(*), sum(cost) from cd group by MONTH Date: Fri, 12 Apr 2002 14:57:55 -0400 From: Jesse Sheidlower [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Newbie GROUP-type question I'm relatively new to the construction of complex queries, and I'm trying to figure one out that I'm sure is common; I wasn't able to find anything especially helpful in the docs. Suppose I have the canonical database of CDs, with one table, which includes fields for title, cost, and date_purchased. I'd like to get a result set that shows the number of CDs purchased, and their sum cost, for each month. Thus, if I bought some CDs that cost exactly $15.00 each month for the last few months, I'll get something like 2001-12 2 30.00 2002-01 3 45.00 2002-02 2 30.00 2002-03 1 15.00 (The exact format isn't too important, I can figure that out once I get the basic query down.) What's the best way to construct this SQL query? Thanks. Jesse Sheidlower - 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 Question
I have a query that is using Group By Title. Is there anyway to make 2 Titles such as League of their own and League of their own### group as the same. I am removing the #'s in the output but I still get 2 results - 1 for each. Ideas? - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: GROUP BY Question (p2)
Sir, try SELECT g1.name, g1.score, g1.id FROM grades g1, grades g2 WHERE g1.name = g2.name GROUP BY g1.name, g1.score HAVING Max(g1.score) = Max(g2.score); Bob Hall Q: I have the following table "grades". ++++ | name | score |id | ++++ | John |10 | 1 | ++++ | John | 20 |2 | ++++ | John | 8 |3 | ++++ | Carl |10 |4 | ++++ | Carl |15 |5 | ++++ | Mary |6 |6 | ++++ I'd like to select a single name, maximum score, as well as the corresponding id number and put them in order from highest to lowest and end up with: ++++ | name | score |id | ++++ | John |20 | 2 | ++++ | Carl |15 |5 | ++++ | Mary |6 |6 | ++++ I tried using: SELECT name, id, max(score) FROM grades GROUP BY name ORDER BY score DESC At first I was pretty happy with this, but I realized that although the name and score where the values I was looking for, the id didn't always correspond to the record I wanted. Any ideas - 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
