How do I (can I) use aggregate functions inside a select
I may be approaching this all wrong, but I need to know a percentage of total sales within a select statement. So I can do something like this: Select company, state, sales, sum(sales) / sales as percent From Sales Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I (can I) use aggregate functions inside a select
I get: Error Code : 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2008 10:37 AM To: David Ruggles Cc: mysql Subject: Re: How do I (can I) use aggregate functions inside a select yes, u can use the below sql. regards anandkl On 7/25/08, David Ruggles [EMAIL PROTECTED] wrote: I may be approaching this all wrong, but I need to know a percentage of total sales within a select statement. So I can do something like this: Select company, state, sales, sum(sales) / sales as percent From Sales Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 6:51 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: How do I (can I) use aggregate functions inside a select
Sum() is driven by the group by. I need it to be equivalent to this: Select company, state, sales, sales / (select sum(sales) from sales) as percent from sales Which of course I could just use that, but the select I'm actually working with isn't that simple and if there was some way to do what I asked below, that would be much easier. I've gathered that is really the only option when it comes down to it so I'm working on writing my select using the above syntax. Thanks all! Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Ian Simpson [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2008 11:15 AM To: David Ruggles Cc: 'mysql' Subject: RE: How do I (can I) use aggregate functions inside a select Hi David, Try Select company, state, sales, sum(sales) / sales as percent From Sales GROUP BY company, state; Not sure if you always want to group by state; if each company exists in only one state then the group by is irrelevant, if not then it will give you the by-state breakdown. On Fri, 2008-07-25 at 10:53 -0400, David Ruggles wrote: I get: Error Code : 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network Engineer Safe Data, Inc. (910) 285-7200[EMAIL PROTECTED] -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2008 10:37 AM To: David Ruggles Cc: mysql Subject: Re: How do I (can I) use aggregate functions inside a select yes, u can use the below sql. regards anandkl On 7/25/08, David Ruggles [EMAIL PROTECTED] wrote: I may be approaching this all wrong, but I need to know a percentage of total sales within a select statement. So I can do something like this: Select company, state, sales, sum(sales) / sales as percent From Sales Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 6:51 AM -- Ian Simpson System Administrator MyJobGroup No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 6:51 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi-table deletes
My SQL: delete c, gt, g, sp from Companies as c, GroupTypes as gt, Groups as g, ServicePersons as sp where gt.companyuid = c.uid and g.companyuid = c.uid and sp.companyuid = c.uid and c.id = '01' This works fine as long as there are records in all tables, but if one of the tables doesn't have any records then the whole delete won't delete any records. I'm trying to clean up all records related to a specific company. Would it be better to do something like this with triggers? Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi-table deletes
I changed my tables to use foreign key constraints with on delete cascade and that did what I needed. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: David Ruggles [mailto:[EMAIL PROTECTED] Sent: Friday, July 18, 2008 11:51 AM To: 'mysql' Subject: Multi-table deletes My SQL: delete c, gt, g, sp from Companies as c, GroupTypes as gt, Groups as g, ServicePersons as sp where gt.companyuid = c.uid and g.companyuid = c.uid and sp.companyuid = c.uid and c.id = '01' This works fine as long as there are records in all tables, but if one of the tables doesn't have any records then the whole delete won't delete any records. I'm trying to clean up all records related to a specific company. Would it be better to do something like this with triggers? Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.1/1560 - Release Date: 7/18/2008 6:47 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How hard is it to move from on server to another?
I have a MySQL 5.x box and I am thinking about moving it to another more powerful server. I would be able to schedule some downtime so that's not an issue. How complicated a process would this be? I don't want to upgrade the software or anything, just move the existing tables, users and permissions to another physical server. It would even have the same IP address. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To replicate or not to replicate that is the question
I need to put a read-only copy of a single table on a database on another server so remote customers can have read access to it. I built a new MySQL server and placed in my DMZ. I can use SSH forwarding to access it from outside. Now I need to get a copy of the table from my internal MySQL server to this new server. I was originally planning on using replication, however the more I research it, the less I think it will do what I want. Here are my requirements: 1) Minimal changes to the internal server, can't take it down. 2) Single table for now, but may need more later. 3) New server will NEVER have write access. 4) Doesn't have to be real-time, but as close as possible would be nice. Does any one have any suggestions, or is replication the way to go? Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie question, how do I respond to post?
Either use reply to all, or manually enter the list email address in the to field. There are two general feelings about list operation: 1) all replies should go to the list 2) all replies should default to the sender This list is configured as #2 (I prefer #1 myself) Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23, 2008 10:19 AM To: mysql@lists.mysql.com Subject: Newbie question, how do I respond to post? Hello, I feel stupid here... How do I respond to a specific post? I posted last night, I got a response... now I want to post a response to the response. Don't see a way to do that... Thanks, Lee ** Start the year off right. Easy ways to stay in shape. http://body.aol.com/fitness/winter-exercise?NCID=aolcmp0030002489 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Packing list sort
I have googled, read the mysql documentation and searched the list archive. I don't know if I just don't know the correct term to use or if I have some other problem. In a nutshell I generate packing lists where the items are normally sorted alphabetically. However, there is one type of item that always gets loaded first. I would like for any of these items to always be at the top of the list. Here's the classic animal example: Given: +--+-++ | name | species | birth | +--+-++ | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +--+-++ I want hamsters to always sort out first, but still have everything else in alphabetical order Like this: +--+-++ | name | species | birth | +--+-++ | Puffball | hamster | 1999-03-30 | | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Slim | snake | 1996-04-29 | +--+-++ Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Packing list sort
Thank you very much, that seems very simple now that I see it. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 10:26 AM To: David Ruggles; 'mysql' Subject: RE: Packing list sort Try One of These (including name): SELECT name,species,birth FROM animals ORDER BY IF(species='hamster',0,1),species,name; OR SELECT name,species,birth FROM (SELECT name,species,birth,IF(species='hamster',0,1) sortorder FROM animals) A ORDER BY sortorder,species,name; -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 10:23 AM To: David Ruggles; 'mysql' Subject: RE: Packing list sort Try One of These: SELECT name,species,birth FROM animalsORDER BY IF(species='hamster',0,1),species; OR SELECT name,species,birth FROM (SELECT name,species,birth,IF(species='hamster',0,1) sortorder FROM animals) A ORDER BY sortorder,species; -Original Message- From: David Ruggles [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 9:53 AM To: 'mysql' Subject: Packing list sort I have googled, read the mysql documentation and searched the list archive. I don't know if I just don't know the correct term to use or if I have some other problem. In a nutshell I generate packing lists where the items are normally sorted alphabetically. However, there is one type of item that always gets loaded first. I would like for any of these items to always be at the top of the list. Here's the classic animal example: Given: +--+-++ | name | species | birth | +--+-++ | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +--+-++ I want hamsters to always sort out first, but still have everything else in alphabetical order Like this: +--+-++ | name | species | birth | +--+-++ | Puffball | hamster | 1999-03-30 | | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Slim | snake | 1996-04-29 | +--+-++ Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [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] -- 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]
Finding a value in multiple tables
I need to find a userid across several different tables. What is the best way to do this? For example I have three tables TableA TableB TableC They all have a field id I want to find the value 123 in the field id in any one of these tables. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding a value in multiple tables
It might have been a dumb question, but I really wasn't sure where to start. Union does exactly what I need! Thanks a bunch. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Wm Mussatto [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 31, 2007 11:56 AM To: mysql@lists.mysql.com Subject: Re: Finding a value in multiple tables On Tue, July 31, 2007 8:52, David Ruggles said: I need to find a userid across several different tables. What is the best way to do this? For example I have three tables TableA TableB TableC They all have a field id I want to find the value 123 in the field id in any one of these tables. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network Engineer Safe Data, Inc. (910) 285-7200[EMAIL PROTECTED] Possibly use a union? I think more details would help. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- 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]
Select into outfile on nfs mount point
I'm unable to select into an outfile, the path is in an nfs mount point. I'm sure it's some sort of permissions issue or something, but I can't figure it out. I've googled everything I can think of and haven't found anything. Any help or suggestions would be greatly appreciated. The error I'm getting is: Error Code : 1 Can't create/write to file './sqldata/test.txt' (Errcode: 524) I see from the mysql site that Error Code 1 = Operation not permitted The sqldata is an nfs mount to another server with full read/write permissions. I can read and write without problem from the commandline as the mysql user. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select into outfile on nfs mount point
Yes, but wouldn't it be the user that the mysql service is running as? That is the user I am logging in as to test this. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 26, 2007 12:01 PM To: David Ruggles Subject: Re: Select into outfile on nfs mount point the file on the mount point has to be able to be created, and read/write by the user that is invoking the mysql call. e.g., if this is begin done at the commandline - then it's the logged-in user (not the user -u user on the mysql command). if via a script, then by whatever user is calling the script (e.g., if via cron, what user is the crontab under). if it's through a web server, then it's the user that the web server is running as. - Rick Original Message Date: Monday, March 26, 2007 11:50:59 AM -0400 From: David Ruggles [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Select into outfile on nfs mount point I'm unable to select into an outfile, the path is in an nfs mount point. I'm sure it's some sort of permissions issue or something, but I can't figure it out. I've googled everything I can think of and haven't found anything. Any help or suggestions would be greatly appreciated. The error I'm getting is: Error Code : 1 Can't create/write to file './sqldata/test.txt' (Errcode: 524) I see from the mysql site that Error Code 1 = Operation not permitted The sqldata is an nfs mount to another server with full read/write permissions. I can read and write without problem from the commandline as the mysql user. -- End Original Message -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select into outfile on nfs mount point
I have verified that the user can read and write as I mentioned in my first post. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 26, 2007 12:37 PM To: David Ruggles Subject: RE: Select into outfile on nfs mount point Date: Monday, March 26, 2007 11:50:59 AM -0400 From: David Ruggles [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Select into outfile on nfs mount point I'm unable to select into an outfile, the path is in an nfs mount point. I'm sure it's some sort of permissions issue or something, but I can't figure it out. I've googled everything I can think of and haven't found anything. Any help or suggestions would be greatly appreciated. The error I'm getting is: Error Code : 1 Can't create/write to file './sqldata/test.txt' (Errcode: 524) I see from the mysql site that Error Code 1 = Operation not permitted The sqldata is an nfs mount to another server with full read/write permissions. I can read and write without problem from the commandline as the mysql user. Sent: Monday, March 26, 2007 12:01 PM the file on the mount point has to be able to be created, and read/write by the user that is invoking the mysql call. e.g., if this is begin done at the commandline - then it's the logged-in user (not the user -u user on the mysql command). if via a script, then by whatever user is calling the script (e.g., if via cron, what user is the crontab under). if it's through a web server, then it's the user that the web server is running as. - Rick From: David Ruggles [EMAIL PROTECTED] Yes, but wouldn't it be the user that the mysql service is running as? That is the user I am logging in as to test this. yes, sorry, i misspoke (have to admit that i don't use outfile much). the file is indeed written by the user running mysqld, generally mysql. make certain that that user can create (e.g., using touch) files in the desired location. - Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select into outfile on nfs mount point
Yes I can touch the file with the mysql user. The only thing I can't do is a select into file. However select into file works anywhere else on the filesystem. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, March 26, 2007 12:06 PM To: 'David Ruggles'; mysql@lists.mysql.com Subject: RE: Select into outfile on nfs mount point Can you touch the file name? It might be a permission issue at the directory level, it has to be writeable. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: David Ruggles [mailto:[EMAIL PROTECTED] Sent: Monday, March 26, 2007 11:51 AM To: mysql@lists.mysql.com Subject: Select into outfile on nfs mount point I'm unable to select into an outfile, the path is in an nfs mount point. I'm sure it's some sort of permissions issue or something, but I can't figure it out. I've googled everything I can think of and haven't found anything. Any help or suggestions would be greatly appreciated. The error I'm getting is: Error Code : 1 Can't create/write to file './sqldata/test.txt' (Errcode: 524) I see from the mysql site that Error Code 1 = Operation not permitted The sqldata is an nfs mount to another server with full read/write permissions. I can read and write without problem from the commandline as the mysql user. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network Engineer Safe Data, Inc. (910) 285-7200[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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting records based on max and value
I'm new to this list, but have searched through the archives and haven't found anything that addresses my question. First a little background: I'm creating a supply ordering system. Each supply order may be modified many times before being approved and finally filled. I created the following databases: Supplyorder uid double (auto) (pk) sitedouble datedate Supplyorderhistory uid double (auto) (pk) supplyorderuid double status double Supplyorderlineitems uid double (auto) (pk) supplyorderhistoryuid double other fields ... Each time an existing supply order is modified a new history record is created so we have a paper trail of all the changes made to the order and the specific line items ordered are associated with the history record. I want to select for a given date all sites that have the last history record with a certain status. This will select the last history record for each site: SELECT MAX(h.uid), site FROM supplyorder AS s, supplyorderhistory AS h WHERE h.supplyorderuid = s.uid AND date = '2007-01-08' GROUP BY s.uid But if I do this: SELECT MAX(h.uid), site FROM supplyorder AS s, supplyorderhistory AS h WHERE h.supplyorderuid = s.uid AND date = '2007-01-08' and status = '2' GROUP BY s.uid Max just becomes the last history record with a status of 2. I understand why it is working this way and that it is working correctly, I just don't know how to get what I want out of it. Thanks in advance for any help. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Selecting records based on max and value
Thanks! I'll make the column type change. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Friday, January 12, 2007 3:42 PM To: David Ruggles Cc: 'mysql' Subject: Re: Selecting records based on max and value David, I want to select for a given date all sites that have the last history record with a certain status. Try something like ... SELECT h1.supplyorderid FROM supplyorder o JOIN supplyorderhistory h1 ON o.uid=h1.supplyorderuid LEFT JOIN supplyorderhistory h2 ON h2.supplyorderuid = h.supplyorderuid AND h1.status=2 AND h1.uid h2.uid WHERE o.date=given_date AND h2.uid IS NULL There's a brief discussion of this query pattern at http://www.artfulsoftware.com/mysql-queries.php; in the left panel click on 'Aggregates' then on 'Within-group aggregates'. BTW your id columns should be ints not doubles, bigints if you expect huge rowcounts. Likewise status. PB - David Ruggles wrote: I'm new to this list, but have searched through the archives and haven't found anything that addresses my question. First a little background: I'm creating a supply ordering system. Each supply order may be modified many times before being approved and finally filled. I created the following databases: Supplyorder uid double (auto) (pk) sitedouble datedate Supplyorderhistory uid double (auto) (pk) supplyorderuid double status double Supplyorderlineitems uid double (auto) (pk) supplyorderhistoryuid double other fields ... Each time an existing supply order is modified a new history record is created so we have a paper trail of all the changes made to the order and the specific line items ordered are associated with the history record. I want to select for a given date all sites that have the last history record with a certain status. This will select the last history record for each site: SELECT MAX(h.uid), site FROM supplyorder AS s, supplyorderhistory AS h WHERE h.supplyorderuid = s.uid AND date = '2007-01-08' GROUP BY s.uid But if I do this: SELECT MAX(h.uid), site FROM supplyorder AS s, supplyorderhistory AS h WHERE h.supplyorderuid = s.uid AND date = '2007-01-08' and status = '2' GROUP BY s.uid Max just becomes the last history record with a status of 2. I understand why it is working this way and that it is working correctly, I just don't know how to get what I want out of it. Thanks in advance for any help. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network Engineer Safe Data, Inc. (910) 285-7200[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.16.10/624 - Release Date: 1/12/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
