Re: Group by column and Sum another
select contract , sum(amlp) from maintenance group by contract; On Mon, Jul 20, 2009 at 6:50 PM, Hagen finha...@comcast.net wrote: I am hoping I can get some help with a query I am trying to construct: I want to group by a 'contract' column and get the sum of the 'amlp' column values associated with each contract. I can do a select and group by on contract select contract from maintenance group by contract; and I will get each unique contract. However, I want to sum the amlp values per unique contract and I am not sure how to construct that select statement. I tried: select contract , sum(amlp) from maintenance where contract=(select contract from maintenance group by contract); But that resulted in a ERROR 1242 (21000): Subquery returns more than 1 row error. Any suggestions? Hagen Finley Boulder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=omel...@gmail.com -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: Does mysql support materialized views?
No, you will have to emulate them using triggers or stored procedures. On Mon, Apr 20, 2009 at 12:28 PM, Lin Chun franks1...@gmail.com wrote: Hi I am now working with Mondrian , and need tuning the database, as the materialized views can improve the performance and doesn't need to create the fact tables cheers -- - Lin Chun -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does mysql support materialized views?
On Mon, Apr 20, 2009 at 12:41 PM, Lin Chun franks1...@gmail.com wrote: Hi Olexandr Melnyk That's to say, create the aggregation tables and update them by triggers? I've noticed a update view in Mysql , can it work under the aggretate operation? You're not looking for views, you need to use normal tables. If you will update the table by triggers, it will always be up-to-date but query cache will be cleared on every such update. On the other hand, if you do it periodically, data will not always be accurate, but query cache can be used efficiently. On Mon, Apr 20, 2009 at 11:34 AM, Olexandr Melnyk omel...@gmail.com wrote: No, you will have to emulate them using triggers or stored procedures. -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- - Lin Chun -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fwd: Does mysql support materialized views?
Query cache stores select query together with its results. When the same query is sent again, result from the cache is returned. Whenever some table is modified, all cache entries referencing it are deleted. That happens even when modification doesn't affect cached rows, eg.: SELECT a FROM a_table WHERE id = 1; # Result gets cached UPDATE a_table SET a = 2 WHERE id = 2; # Result is removed from the query cache SELECT a FROM a_table WHERE id = 1; # Query cache can't be used On Mon, Apr 20, 2009 at 12:56 PM, Lin Chun franks1...@gmail.com wrote: Hi I don't know very well MySQL, the query cache is something like the Snapshotone of the s mod listed in the query is modified, the in Oracle, so we can get the result immediately? On Mon, Apr 20, 2009 at 11:47 AM, Olexandr Melnyk omel...@gmail.com wrote: On Mon, Apr 20, 2009 at 12:41 PM, Lin Chun franks1...@gmail.com wrote: Hi Olexandr Melnyk That's to say, create the aggregation tables and update them by triggers? I've noticed a update view in Mysql , can it work under the aggretate operation? You're not looking for views, you need to use normal tables. If you will update the table by triggers, it will always be up-to-date but query cache will be cleared on every such update. On the other hand, if you do it periodically, data will not always be accurate, but query cache can be used efficiently. On Mon, Apr 20, 2009 at 11:34 AM, Olexandr Melnyk omel...@gmail.com wrote: No, you will have to emulate them using triggers or stored procedures. -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- - Lin Chun -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- - Lin Chun -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: displaying a specific row within a group by
Also: http://jan.kneschke.de/projects/mysql/groupwise-max On Thu, Apr 9, 2009 at 8:46 PM, Peter Brawley peter.braw...@earthlink.net wrote: Andy I want a single SQL query that will return the latest ranking for each game: See Within-group aggregates at http://www.artfulsoftware.com/infotree/queries.php. PB - Andy Sy wrote: Consider the ff. table: ++--+--+ | game | rank | date | ++--+--+ | GTA | 11 | 20081001 | ++--+--+ | SPORE | 1 | 20081103 | ++--+--+ | SPORE | 2 | 20091001 | ++--+--+ | SINSOL | 8 | 20081011 | ++--+--+ | SINSOL | 31 | 20080808 | ++--+--+ | SPORE | 50 | 20090402 | ++--+--+ | SINSOL | 11 | 20090104 | ++--+--+ | GTA | 21 | 20080821 | ++--+--+ | WOW | 1 | 20080922 | ++--+--+ | WOW | 11 | 20081023 | ++--+--+ | WOW | 15 | 20090106 | ++--+--+ I want a single SQL query that will return the latest ranking for each game: ++--+--+ | game | rank | date | ++--+--+ | SPORE | 50 | 20090402 | ++--+--+ | SINSOL | 11 | 20090104 | ++--+--+ | GTA | 21 | 20080821 | ++--+--+ | WOW | 15 | 20090106 | ++--+--+ How do I go about it? The initial 'obvious' solution: select game,rank,max(date) from gametbl group by game DOESN'T work because the 'rank' value we get will not necessarily come from the same row holding the 'max(date)'!! Instead, you have to sort by date first in a subquery before applying the GROUP BY: select * from ( select * from gametbl order by date desc ) as t group by game This seems to work in MySQL but I do not trust this construction because it relies on ordering, which relational philosophy is supposed to not depend on. (Will this really safely work on all proper SQL implementations?) Another reason I don't like it is because it relies on a subquery and I was wondering if it is possible to do away with that. I can also imagine a solution relying on a self-join which does not depend on sorting, but which would require a surrogate primary key which I find even less elegant than relying a subquery. = The Webmechs Webpress blog http://www.webmechs.com/webpress/ No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.238 / Virus Database: 270.11.49/2050 - Release Date: 04/09/09 10:27:00 -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: DELETED records still exist?
Probably records were deleted in a transaction, which was later rolled back? 2009/1/25 Ariela Levy ariela_l...@camh.net Hi there, I'm running an ecommerce site and have manually deleted customer records from mysql database however it seems these records still exist because my authentication routine for login is finding a match for the primary key in the customer table ( email). Does anyone have an idea whether this could be an indexing problem or a memory problem and how I can resolve it? Thank you kindly Ariela __ This email has been scanned by the CAMH Email Security System. __ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: SELECT through many databases
Hello Andre,
I would recommend you to rethink your criteria (if there's any) for
splitting data into multiple tables.
Because now. the more tables you add, the more of a performance problem it
may cause.
--
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/
On Fri, Nov 21, 2008 at 9:58 PM, Andre Matos [EMAIL PROTECTED]wrote:
Thanks everyone for the help.
I was trying to use any API (e.g. PHP, Java, etc). I just gave up because
unfortunately the time fly and the user needs this asap. I am doing via
script.
Thanks!
Andre
On 21-Nov-08, at 2:34 PM, Jerry Schwartz wrote:
Many MySQL APIs (such as PHP) allow you to treat the result returned from
a
SHOW statement as you would a result set from a SELECT; see Chapter 22,
APIs
and Libraries, or your API documentation for more information. In
addition,
you can work in SQL with results from queries on tables in the
INFORMATION_SCHEMA database, which you cannot easily do with results from
SHOW statements. See Chapter 20, The INFORMATION_SCHEMA Database.
You should be able to refresh your MERGE table using a programming
language. Get a list of the databases, then construct a CREATE TABLE or
what
have you.
-Original Message-
From: Andre Matos [mailto:[EMAIL PROTECTED]
Sent: Friday, November 21, 2008 2:11 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: SELECT through many databases
I was trying to avoid both since the SELECT statement is not fixed.
Time to time, users want different information.
Thanks,
Andre
On 21-Nov-08, at 12:59 PM, Peter Brawley wrote:
Andre Matos wrote:
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any database.
Do it in an app language or as a PREPARED statement in an sproc.
PB
---
Andre Matos wrote:
Hello,
Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They
all have the same structure but different data.
I would like perform this select
SELECT TaskDoneOn, TaskDoneBy
FROM {database}
WHERE TaskDoneOn IS NOT NULL
and collect the data from all 5 database. However, I would like to
avoid doing something like this:
SELECT TaskDoneOn, TaskDoneBy
FROM db1
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db2
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db3
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db4
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db5
WHERE TaskDoneOn IS NOT NULL
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any database.
Thanks for any help.
Andre
-
---
Internal Virus Database is out of date.
Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus
Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT through many databases
Unless that's disallowed too, you could use a couple of triggers which would
update the stats table(s) on insert/delete/update to the main tables in each
database.
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/
On Fri, Nov 21, 2008 at 10:31 PM, Andre Matos [EMAIL PROTECTED]wrote:
The reason for having many databases with the same structure but with
different data is because for regulatory and compliance requirements. Each
database belongs to a separate company: company specific database. The
applications (some written in PHP4 and others in J2EE) are unique and they
can access one database each time. Connect to one database and then
disconnect to connect to a different one.
The issue is when I need to collect some data for statistics purpose.
Unfortunately, I cannot have an official application to go through all
database or even use the MERGE table as suggested here in the list. I am not
allowed to. That's why I was thinking to use only a SELECT statement to do
the job as I have been doing.
Thanks,
Andre
On 21-Nov-08, at 3:16 PM, Olexandr Melnyk wrote:
Hello Andre,
I would recommend you to rethink your criteria (if there's any) for
splitting data into multiple tables.
Because now. the more tables you add, the more of a performance problem it
may cause.
--
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/
On Fri, Nov 21, 2008 at 9:58 PM, Andre Matos [EMAIL PROTECTED]
wrote:
Thanks everyone for the help.
I was trying to use any API (e.g. PHP, Java, etc). I just gave up because
unfortunately the time fly and the user needs this asap. I am doing via
script.
Thanks!
Andre
On 21-Nov-08, at 2:34 PM, Jerry Schwartz wrote:
Many MySQL APIs (such as PHP) allow you to treat the result returned
from
a
SHOW statement as you would a result set from a SELECT; see Chapter 22,
APIs
and Libraries, or your API documentation for more information. In
addition,
you can work in SQL with results from queries on tables in the
INFORMATION_SCHEMA database, which you cannot easily do with results
from
SHOW statements. See Chapter 20, The INFORMATION_SCHEMA Database.
You should be able to refresh your MERGE table using a programming
language. Get a list of the databases, then construct a CREATE TABLE or
what
have you.
-Original Message-
From: Andre Matos [mailto:[EMAIL PROTECTED]
Sent: Friday, November 21, 2008 2:11 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: SELECT through many databases
I was trying to avoid both since the SELECT statement is not fixed.
Time to time, users want different information.
Thanks,
Andre
On 21-Nov-08, at 12:59 PM, Peter Brawley wrote:
Andre Matos wrote:
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any database.
Do it in an app language or as a PREPARED statement in an sproc.
PB
---
Andre Matos wrote:
Hello,
Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They
all have the same structure but different data.
I would like perform this select
SELECT TaskDoneOn, TaskDoneBy
FROM {database}
WHERE TaskDoneOn IS NOT NULL
and collect the data from all 5 database. However, I would like to
avoid doing something like this:
SELECT TaskDoneOn, TaskDoneBy
FROM db1
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db2
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db3
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db4
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db5
WHERE TaskDoneOn IS NOT NULL
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any database.
Thanks for any help.
Andre
-
---
Internal Virus Database is out of date.
Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus
Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Dr. André Matos
[EMAIL PROTECTED]
Re: how to generate the following output in sql ?
Helllo Supratim, does each pub_code appear maximum three times in the table? 2008/11/10, Supratim Bandyopadhyaya [EMAIL PROTECTED]: I am really really new to sql,please forgive my ignorence. here is my problem I have this table named mis_outtrack desc mis_outtrack; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | pub_code| varchar(3) | YES | | NULL| | | status_flag | varchar(2) | YES | | NULL| | | user_id | varchar(15) | YES | | NULL| | | entry_date | date| YES | | NULL| | | pub_date| date| YES | | NULL| | +-+-+--+-+-+---+ select * from mis_outtrack; +--+-+-+++ | pub_code | status_flag | user_id | entry_date | pub_date | +--+-+-+++ | SND | RE | AMY | 2008-11-01 | 2008-11-01 | | SND | AU | AMY | 2008-11-05 | 2008-11-01 | | SND | SE | AMY | 2008-11-10 | 2008-11-01 | | MND | RE | AMY | 2008-11-03 | 2008-11-02 | | MND | AU | AMY | 2008-11-15 | 2008-11-02 | | MND | SE | AMY | 2008-11-15 | 2008-11-02 | +--+-+-+++ here pub_code is short name for a particular news paper useid is the user who is updating the records pub_date is the date of publication of that news paper and entry_date is the date when the user is updating the inserting the status of that publication here RE means recieved AU means audited SE means send there can only be these three status flags I have to write an sql to display this data in the following format pub_code |entry_date |entry_date|entry_date|pub_date | (for a |(when |(when|(when |(publication | particular |status was RE)|status was AU)|status was SE |date for | pub_code) | | | |that news paper| | SND2008-11-012008-11-05 2008-11-102008-11-01 | MND2008-11-032008-11-15 2008-11-15 2008-11-02 | | how can I do this in sql any help will be appriciated. Thanks in advance -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: ORDER BY Help
SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: ORDER BY Help
SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Following on from my email below I now need help with the following problem. Here is a list of my sample data Date ProductID ProductNameProductScore Quantity 2008-11-10100 Red Light 0.05 10 2008-11-11100 Red Light 0.05 2 2008-11-12100 Red Light 0.05 0 2008-11-10150 Blue Light 0.01 5 2008-11-11150 Blue Light 0.01 5 2008-11-12150 Blue Light 0.01 5 2008-11-10160 Green Light 0.055 2008-11-11160 Green Light 0.065 2008-11-12160 Green Light 0.115 I need to list this data in the order of the the product with the highest quantity, followed by ProductScore. Am I able to calculate a quantity percentage, based on the number of records for say Red Light. Thanks, Neil On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED] wrote: Thanks for the reply, this is exactly what I wanted. Cheers Olexandr ! On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: ORDER BY Help
Could give us sample values for a field? Should it contain the same thing as in the query I've sent? 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Hi This works, however I still want to be able to list the whole list like because I need to display it on the screen, but in the ordered together i.e all RedLights, all BlueLights etc a Date ProductID ProductName ProductScore Quantity %2008-11-10100 Red Light 0.05 10 %2008-11-11100 Red Light 0.05 2 %2008-11-12100 Red Light 0.05 0 %2008-11-10150 Blue Light 0.01 5 %2008-11-11150 Blue Light 0.01 5 %2008-11-12150 Blue Light 0.01 5 %2008-11-10160 Green Light 0.055 %2008-11-11160 Green Light 0.065 %2008-11-12160 Green Light 0.115 Is this possible ? On Fri, Oct 24, 2008 at 2:28 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Following on from my email below I now need help with the following problem. Here is a list of my sample data Date ProductID ProductNameProductScore Quantity 2008-11-10100 Red Light 0.05 10 2008-11-11100 Red Light 0.05 2 2008-11-12100 Red Light 0.05 0 2008-11-10150 Blue Light 0.01 5 2008-11-11150 Blue Light 0.01 5 2008-11-12150 Blue Light 0.01 5 2008-11-10160 Green Light 0.055 2008-11-11160 Green Light 0.065 2008-11-12160 Green Light 0.115 I need to list this data in the order of the the product with the highest quantity, followed by ProductScore. Am I able to calculate a quantity percentage, based on the number of records for say Red Light. Thanks, Neil On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED] wrote: Thanks for the reply, this is exactly what I wanted. Cheers Olexandr ! On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: ORDER BY Help
Still doesn't make much sense to me. Could you show us how to calculate it for some of the rows above? 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Hi Thanks for your quick reply. The sample value for a would be like a average of integer. e.g 6.01, or 10.19. Neil On Fri, Oct 24, 2008 at 2:49 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: Could give us sample values for a field? Should it contain the same thing as in the query I've sent? 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Hi This works, however I still want to be able to list the whole list like because I need to display it on the screen, but in the ordered together i.e all RedLights, all BlueLights etc a Date ProductID ProductName ProductScore Quantity %2008-11-10100 Red Light 0.05 10 %2008-11-11100 Red Light 0.05 2 %2008-11-12100 Red Light 0.05 0 %2008-11-10150 Blue Light 0.01 5 %2008-11-11150 Blue Light 0.01 5 %2008-11-12150 Blue Light 0.01 5 %2008-11-10160 Green Light 0.055 %2008-11-11160 Green Light 0.065 %2008-11-12160 Green Light 0.115 Is this possible ? On Fri, Oct 24, 2008 at 2:28 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Following on from my email below I now need help with the following problem. Here is a list of my sample data Date ProductID ProductNameProductScore Quantity 2008-11-10100 Red Light 0.05 10 2008-11-11100 Red Light 0.05 2 2008-11-12100 Red Light 0.05 0 2008-11-10150 Blue Light 0.01 5 2008-11-11150 Blue Light 0.01 5 2008-11-12150 Blue Light 0.01 5 2008-11-10160 Green Light 0.055 2008-11-11160 Green Light 0.065 2008-11-12160 Green Light 0.115 I need to list this data in the order of the the product with the highest quantity, followed by ProductScore. Am I able to calculate a quantity percentage, based on the number of records for say Red Light. Thanks, Neil On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED] wrote: Thanks for the reply, this is exactly what I wanted. Cheers Olexandr ! On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: MyISAM optimize
Hello Olaf, 1) OPTIMIZE TABLE is the same as mysqlcheck with --optimize flag. 2) Both take care of large chunks of deleted data. 3) As mysqlcheck is just a frontend for OPTIMIZE TABLE command, it should be replicated in either case. 2008/10/15, Olaf Stein [EMAIL PROTECTED]: Hi All, Just some simple questions I am somehow not able to find an answer to. - Does optimize table tablename do the same thing as mysqlcheck [options] db_name [tables] ? - Do they take care of large chunks of deleted data? - and I am assuming optimize table would replicate, mysqlcheck I would have to run on all slaves, correct? Thanks Olaf - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: Problem with GROUP BY
http://jan.kneschke.de/projects/mysql/groupwise-max 2008/10/14 Peter Brawley [EMAIL PROTECTED] Philip mysql SELECT number, MAX(event), name FROM info GROUP BY number; For discussion examples see Within-group aggregates at http://www.artfulsoftware.com/queries.php. PB - philip wrote: I created a table with, CREATE TABLE info ( number INTEGER UNSIGNED, event INTEGER UNSIGNED, name VARCHAR(2000) NOT NULL, PRIMARY KEY (number, event) ); and populated it with data to produce this, ++---+---+ | number | event | name | ++---+---+ | 67 | 1 | Alice | | 67 | 2 | Bob | | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 1 | Bob | | 72 | 2 | Alice | | 72 | 3 | David | ++---+---+ What I want to produce is a table with rows from the original with only the maximum value of event for each corresponding number selected, like this +++---+ | number | event | name | +++---+ | 67 | 2 | Bob | | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 3 | David | +++---+ The closest I have managed to produce using GROUP BY is, mysql SELECT number, MAX(event), name FROM info GROUP BY number; +++---+ | number | MAX(event) | name | +++---+ | 67 | 2 | Alice | - should be Bob | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 3 | Bob | - should be David +++---+ I tried using a HAVING clause but got nowhere. Can anybody help please ? TTFN, Philip Riebold, [EMAIL PROTECTED] /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (switchboard), 09259 (internal) Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM -- -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: Tables are too wide
select * from mysql.db\G 2008/9/27, Alex Katebi [EMAIL PROTECTED]: Hi, When selecting (example: select * from mysql.db ) some of the mysql database tables the result set does not fit in a standard xterm window. Is there any terminal display program in Linux that has a horizontal scroll bar that I can use? Or what can I do to split the table output to fit on my screen? Thanks, -Alex -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: Relational Databasing on busy webserver
On 9/23/08, David Ashley [EMAIL PROTECTED] wrote: For example, for the 50 states, a lot of programmers would put this logic in the web script and just store the two-letter postal code in the database table (but with no separate table for states). The mapping from MI to Michigan would occur in the web scripts; as would the logic to know that XQ is not a state. Keep in mind that, as you add more countries, maintaining in-scipt code/name mappings of state codes will become more and more cumbersome. Another problem will arise if you want to view user information, along with full country and state names, from a different programming language. Plus, if the same query is run very often and table is almost static, chances are high that the result will be in query cache. -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: Relational Databasing on busy webserver
On Tue, Sep 23, 2008 at 6:13 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: Plus, if the same query is run very often and table is almost static, chances are high that the result will be in query cache. Just realized that I haven't mentioned that this sentence is related to storing states in the database, rather than in the application layer. -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: too many connections
Restart MySQL server On 9/19/08, Kinney, Gail [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: too many connections
In case you're using PHP, in theory all database connections should be closed when script stops execution. I'm not sure if it's always like that in practice. Persistent connections can be a quick fix to your problem, but as was mentioned in the previous mail, it's better to find out why there's so many of them. On 9/19/08, Gary W. Smith [EMAIL PROTECTED] wrote: Gail, I know the list has already recommended allowing more connections but the bigger question is what is sucking them all up. Even with 1000 connections things like apache can only use the number of connections that there are processes (* the number of connections used within each process). As a fast workaround, increase the connections but for a long term solution you really need to find out what the problem is, now how to work around it. Gary From: Kinney, Gail [mailto:[EMAIL PROTECTED] Sent: Fri 9/19/2008 8:33 AM To: 'mysql@lists.mysql.com' Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: too many connections
Then killing the server process should be safe. Except that server startup may take a while. On Fri, Sep 19, 2008 at 7:25 PM, Kinney, Gail [EMAIL PROTECTED]wrote: We are using the default storage engine - INNODB *From:* Olexandr Melnyk [mailto:[EMAIL PROTECTED] *Sent:* Friday, September 19, 2008 9:44 AM *To:* Kinney, Gail *Subject:* Re: too many connections Are there any UPDATE queries being executed? Which storage engines are you using? On 9/19/08, *Kinney, Gail* [EMAIL PROTECTED] wrote: Ok, we tried that but we are getting and error that it can't be stopped (timed out), although status says that it is stopping. Do we need to reboot the entire machine? *From:* Olexandr Melnyk [mailto:[EMAIL PROTECTED] *Sent:* Friday, September 19, 2008 9:40 AM *To:* Kinney, Gail *Subject:* Re: too many connections Yes, that's what I was referring to On 9/19/08, *Kinney, Gail* [EMAIL PROTECTED] wrote: Thanks you for responding. Our MySQL is on a web server for the entire campus. Can we just restart the service? *From:* Olexandr Melnyk [mailto:[EMAIL PROTECTED] *Sent:* Friday, September 19, 2008 9:38 AM *To:* Kinney, Gail; mysql@lists.mysql.com *Subject:* Re: too many connections Restart MySQL server On 9/19/08, *Kinney, Gail* [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: CONCAT doesn't work with NULL?
It doesn't return no rows, it returns row(s) with a single column set to a NULL value. In case one of the arguments is NULL, CONCAT() will return NULL. To replace the value of one of the fields with an empty string when it's NULL, you can use something like: CONCAT(COAESCE(a, ''), ' ', COAESCE(b, '')) On 5/14/08, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Indices in InnoDB/MySQL
On 4/1/08, Paul DuBois [EMAIL PROTECTED] wrote: At 10:01 AM -0700 4/1/08, Robert DiFalco wrote: I've been told that an index always contains the primary key. By who? Ask for proof. I guess he was referring to the fact that InnoDB stores the primary key values alongside the indexed columns value, as a way of referencing the associated row. -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: MySQL and MS SQL Server 2005
Most of the basic statements should work fine on both, but here are several points you should keep in mind: - SQL Server doesn't support LIMIT clause; - Don't use quotes for numerical values as it works on MySQL only; - In GROUP BY clause, list all not aggregated columns you are selecting; - Use single quotes for strings; Hope it helps. On 1/23/08, J Trahair [EMAIL PROTECTED] wrote: Hi Everyone I am now familiar with connecting with MySQL using Visual Basic 6 and ADO. I am about to start a new project which must connect to a customer's MS SQL Server 2005 installation. How safe/unsafe is my assumption that as far as SQL strings and ADO connections are concerned 'if it works on MySQL it will work on MS SQL Server 2005'? The SQL strings I use are the really simple ones eg. INSERT INTO..., UPDATE FROM...,SET...WHERE... SELECT FROM...WHERE...ORDER BY... Thanks in advance. Jonathan Trahair -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: MySQL and MS SQL Server 2005
SQL Server doesn't support the IF EXISTS clause. There are also differences in data types. On 1/23/08, J Trahair [EMAIL PROTECTED] wrote: What about DROP TABLE IF EXISTS TableName and CREATE TABLE NewTableName... and ALTER TABLE TableName ADD COLUMN DeliveryNoteNumber INT NOT NULL DEFAULT 0 Thanks Jonathan Trahair Most of the basic statements should work fine on both, but here are several points you should keep in mind: - SQL Server doesn't support LIMIT clause; - Don't use quotes for numerical values as it works on MySQL only; - In GROUP BY clause, list all not aggregated columns you are selecting; - Use single quotes for strings; Hope it helps. On 1/23/08, J Trahair [EMAIL PROTECTED] wrote: Hi Everyone I am now familiar with connecting with MySQL using Visual Basic 6 and ADO. I am about to start a new project which must connect to a customer's MS SQL Server 2005 installation. How safe/unsafe is my assumption that as far as SQL strings and ADO connections are concerned 'if it works on MySQL it will work on MS SQL Server 2005'? The SQL strings I use are the really simple ones eg. INSERT INTO..., UPDATE FROM...,SET...WHERE... SELECT FROM...WHERE...ORDER BY... Thanks in advance. Jonathan Trahair -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Fwd: auto increment format
You can do that using a before insert trigger, something like (untested) : CREATE TRIGGER test1bi BEFORE INSERT ON test1 FOR EACH ROW BEGIN NEW.ID = COALESCE( NEW.ID, SHA1(CAST(RAND() AS CHAR))) END; 2007/8/7, shivendra [EMAIL PROTECTED]: Hi, I'm looking for some basic help. I am developing a MySQL database and want to auto increment a field, but I don't want it to just count 1,2,3, etc. I want the field to be a combination of letters and numbers, at least 8 digits long, completely random for security porposes, but do this automatically, everytime a record is added. For example, ord5001, ord5002, ord5003, etc. Does anyone know how to do this in MySQL? -- View this message in context: http://www.nabble.com/auto-increment-format-tf4229677.html#a12032917 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: consitent backup of MyISAM and Innodb tables
Check out this thread: http://www.sitepoint.com/forums/showpost.php?p=3357628postcount=2 2007/7/17, [EMAIL PROTECTED] [EMAIL PROTECTED]: Hello, we have a MySQL DBMS with a lot of databases. Most of them are using MyISAM tables but three databases use InnoDB and MyISAM tables. What is the best method to get a consitent ONLINE backup of both table types? Thanks, Spiker -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: giving a row the new highest id
update table mytable set id =last_insert_id() + 1 where id = $oldid 2007/7/11, Olav Mørkrid [EMAIL PROTECTED]: using one single sql statement, how do i update the auto_increment id column of a row to have the new highest id in the table? in other words: how do i make a row seem like it was just inserted? i know how to do it with two statements, but i want to do it with one to ensure nothing goes wrong: $newid = get_value(select max(id) from user) + 1; run_query(update table mytable set id = '$newid' where id = '$oldid' ); hope someone can help. thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: giving a row the new highest id
Err.. you can do this: update table mytable set id = (select max(id) + 1 from user) where id = $oldid but I would recommend to use a transaction 2007/7/11, Olexandr Melnyk [EMAIL PROTECTED]: update table mytable set id =last_insert_id() + 1 where id = $oldid 2007/7/11, Olav Mørkrid [EMAIL PROTECTED]: using one single sql statement, how do i update the auto_increment id column of a row to have the new highest id in the table? in other words: how do i make a row seem like it was just inserted? i know how to do it with two statements, but i want to do it with one to ensure nothing goes wrong: $newid = get_value(select max(id) from user) + 1; run_query(update table mytable set id = '$newid' where id = '$oldid' ); hope someone can help. thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: giving a row the new highest id
2007/7/11, Olav Mørkrid [EMAIL PROTECTED]: thanks olexandr my posting had a misprint. the select should be on mytable not user, so when i use your suggestion, i get an error: mysql update test set id = (select max(id) + 1 from test) where id = '$myid'; ERROR 1093 (HY000): You can't specify target table 'test' for update in FROM clause so how does one make a row id to appear as a newly inserted row, without doing multiple queries? why do you need this? you can do insert and use last_ionsert_id() within a transaction (in case storage engine you're using supports it) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: selecting everyting from 2 non-identical tables.
select id, name, age, null as height from table1 union select id, name, null as age, height from table2 2007/6/19, [EMAIL PROTECTED] [EMAIL PROTECTED]: select * from table1, table2 seems to give repeat rows for some reason. It is a Cartesian product. -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: selecting everyting from 2 non-identical tables.
2007/6/19, [EMAIL PROTECTED] [EMAIL PROTECTED]: My frist post was not worded correctly. I cannot join two tables as all the rows are unique. What's wrong with my solution? 2007/6/19, Olexandr Melnyk [EMAIL PROTECTED]: select id, name, age, null as height from table1 union select id, name, null as age, height from table2 -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: Object-Oriented database
You can store any object as blob, but that's not a good idea (unless object is atomic in terms of your application). If you want to refer to your data in object-oriented manner, you can use one of the ORM pattern implementations on the client sise. As for a separate data type, no, MySQL doesn't have it. - Olexandr Melnyk, http://omelnyk.net/ 2007/5/4, sam rumaizan [EMAIL PROTECTED]: Is there such thing call Object-Oriented database for mysql? Basically can I store an item as an object in myql? - Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center.
Database with a lot of views
In our application, each user has an won set of tables. We're migrating to a shared table set, with an extra field in each table, identifying the user a particular rows belongs to. In order to keep database changes minimal, we're thinking of using an own set of views for each user. Is there huge overhead in database containing a lot (millions) of views? Yours faithfully, Olexandr Melnyk
Re: Group By / Order BY
1) ORDER BY is executed after GROUP BY; 2) In ORDER BY don't use columns that aren't in GROUP BY, unless it's an aggregated value; Your query can be rewritten as: select date , max(rev) as max_rev from table group by date order by max_rev desc 2007/3/17, Justin [EMAIL PROTECTED]: I've got an issue with group / order by.. here's what I'm wondering.. simple table.. date | rev -- 20070315 1 20070315 2 20070316 1 20070316 2 Query I'm running SELECT * FROM `table` GROUP BY `date` order by `rev` DESC I would think this would return 20070315 - 2 20070316 - 2 but it doesn't it returns 20070315 - 1 20070316 - 1 What am I missing? I'm trying to do a group by the date and return the higher rev.. thanks agian! Justin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Olexandr Melnyk, http://omelnyk.net/
Performace of a database with a lot of tables
Hello, I am working on hosting solution for a popular open source script, where users can sign up and get an own forum. We use InnoDB, and each user has an own set of tables (around 30); there are currently around 500 users registered. Despite the large count of tables, database is small, around 50 MB. Server is dual Xeon with 4GB of RAM and MySQL uses all memory, available on the server, so there's even nothing left for Apache. As the user base is growing, what steps would you suggest me to do, in order to keep good database performance and make it more efficient on RAM usage? The former is more important. Olexandr Melnyk, http://omelnyk.net/
Re: problem with update innodb table
Posting you table difinitions and queries would be helpful. Olexandr Melnyk, http://omelnyk.net/ 2007/2/8, Alicia Amadoz [EMAIL PROTECTED]: Hello, I am trying to update two fields of a table that are a composed foreign key to another table. These fields cant be updated with this kind of warning: Warning | 1292 | Truncated incorrect DOUBLE value: 'A05' Both tables are InnoDB and I have tried with all types of constraints, ON UPDATE CASCADE, SET NULL and NO ACTION, as foreign key constraints of the table that I want to update. Any help or idea of how can I solve this problem? Thank you very much in advance, Alicia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT from 3 tables - Need help
2007/1/18, Nuno Oliveira [EMAIL PROTECTED]: Hi, thanks for the reply (you should reply to the list also) Heh, that was the default Gmail behaviour. Do you mean that I should replace SELECT * FROM with SELECT field1, [field2],[...] FROM? It is generally a good pratice to keep away from queries with asterisks in production systems. And, as Chris mentioned, it is also recommended to always specify the table name for multi-table queries. Olexandr Melnyk, http://omelnyk.net/
