Mysql HA
Hi, Is it possible to implement an HA configuration in mysql without using Mysql Clustering? A howto is very much appreciated.. thanks, Mailing-List -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to sort last n entries?
I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? Thanks for your help Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort last n entries?
At 10:41 +0200 15/9/06, Dominik Klein wrote: I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? ORDER BY `date` DESC LIMIT n -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ A censor is a man who knows more than he thinks you ought to. -- Laurence J. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql HA
Sure. MySQL runs just fine in an Active /Passive configuration for HA. There is a white paper that you can request from the MySQL homepage among the other white papers here: http://www.mysql.com/why-mysql/white-papers/ For such a configuration, a popular choice is to use Linux HA. You can read more about that on the Linux HA homepage at: http://www.linux-ha.com/ There are a few different way to adopt MySQL in this type of environment. You can use DRBD (which is part of the Linux HA project) or a SAN or you can use MySQL Replication. Best regards Anders Karlsson JM wrote: Hi, Is it possible to implement an HA configuration in mysql without using Mysql Clustering? A howto is very much appreciated.. thanks, Mailing-List -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show differences between two tables
Hi I've two tables TableA FieldID Enabled TableB FieldID Enabled What query would I need to show what FieldIDs which are the same, but the enabled field status is different ?Thanks, Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
Re: How to sort last n entries?
Chris Sansom schrieb: At 10:41 +0200 15/9/06, Dominik Klein wrote: I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? ORDER BY `date` DESC LIMIT n Last n entries means I want the last (highest) n ids. And that result sorted by date. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort last n entries?
At 10:41 +0200 15/9/06, Dominik Klein wrote: I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? ORDER BY `date` DESC LIMIT n Last n entries means I want the last (highest) n ids. And that result sorted by date. ORDER BY `id` DESC, `date` ASC LIMIT n I think :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to sort last n entries?
SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 3:41 PM To: mysql@lists.mysql.com Subject: How to sort last n entries? I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? Thanks for your help Dominik -- 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]
FW: How to sort last n entries?
Assuming your MySQL version supports sub queries you do like this. I have never done sub queries my self, but I know the theory :) SELECT * FROM table WHERE id = (SELECT id FROM table ORDER BY id DESC) ORDER BY date /Peter Lauri www.lauri.se - personal www.dwsasia.com - company (Web Development Bangkok Thailand) -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 3:41 PM To: mysql@lists.mysql.com Subject: How to sort last n entries? I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? Thanks for your help Dominik -- 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: How to sort last n entries?
And if your MySQL version does NOT support sub queries you can probably just create a temporary table and then sort that one. /Peter -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 4:28 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: FW: How to sort last n entries? Assuming your MySQL version supports sub queries you do like this. I have never done sub queries my self, but I know the theory :) SELECT * FROM table WHERE id = (SELECT id FROM table ORDER BY id DESC) ORDER BY date /Peter Lauri www.lauri.se - personal www.dwsasia.com - company (Web Development Bangkok Thailand) -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 3:41 PM To: mysql@lists.mysql.com Subject: How to sort last n entries? I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? Thanks for your help Dominik -- 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]
Re: How to sort last n entries?
Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to sort last n entries?
You are correct. So that maybe leaves you with a temporary table then :) -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 4:45 PM To: mysql@lists.mysql.com Subject: Re: How to sort last n entries? Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- 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: How to sort last n entries?
CREATE TEMPORARY TABLE tabletemp SELECT * FROM table ORDER BY id DESC LIMIT 30; SELECT * FROM tabletemp ORDER BY date; -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 4:45 PM To: mysql@lists.mysql.com Subject: Re: How to sort last n entries? Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- 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: Show differences between two tables
Hi Neil - Something like this ought to work, joining on the ID column to find matches between tables, then finding the enabled fields which are not equal. SELECT a.fieldID, a.enabled, b.enabled FROM TableA a, TableB b WHERE a.fieldID = b.fieldID AND a.enabled != b.enabled Dan On 9/15/06, Neil Tompkins [EMAIL PROTECTED] wrote: Hi I've two tables TableA FieldID Enabled TableB FieldID Enabled What query would I need to show what FieldIDs which are the same, but the enabled field status is different ?Thanks, Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
RE: Show differences between two tables
This may be the blind leading the one-eyed, but wouldn't SELECT FieldID FROM TableA, TableB WHERE TableA.FieldID = TableB.FieldID AND TableA.Enabled != TableB.Enabled; Work? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Neil Tompkins [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 5:04 AM To: mysql@lists.mysql.com Subject: Show differences between two tables Hi I've two tables TableA FieldID Enabled TableB FieldID Enabled What query would I need to show what FieldIDs which are the same, but the enabled field status is different ?Thanks, Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-491 1fb2b2e6d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort last n entries?
This might work, I've used this syntax to select and sort from a UNION. SELECT * FROM (SELECT * FROM table ORDER BY id DESC LIMIT n) AS ltable ORDER BY datefield DESC According to the documentation you can use limit in subqueries: A subquery can contain any of the keywords or clauses that an ordinary SELECT can contain: DISTINCT, GROUP BY, ORDER BY, LIMIT,... - Original Message - From: Dominik Klein [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 15, 2006 5:44 AM Subject: Re: How to sort last n entries? Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- 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]
Sort Problem
I have the following query that has worked fine for displaying standings for a soccer league. SELECT * FROM standings WHERE division = 'BU10' AND pool = '1' ORDER BY tpts DESC, spts DESC, w DESC, ga ASC, team_number ASC As I said, works fine. Now, however, the league wants a slightly different sort order. They only want to sort on the spts column if the difference between 2 teams is greater than 9 in the spts column. All other sort criteria remain the same. So, the ORDER BY would be tpts DESC, spts DESC (but only if the difference is 9), w DESC, ga ASC, team_number ASC. If it is possible to form such a query, I need help in what is would look like. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
Albert Padley wrote: I have the following query that has worked fine for displaying standings for a soccer league. SELECT * FROM standings WHERE division = 'BU10' AND pool = '1' ORDER BY tpts DESC, spts DESC, w DESC, ga ASC, team_number ASC As I said, works fine. Now, however, the league wants a slightly different sort order. They only want to sort on the spts column if the difference between 2 teams is greater than 9 in the spts column. All other sort criteria remain the same. So, the ORDER BY would be tpts DESC, spts DESC (but only if the difference is 9), w DESC, ga ASC, team_number ASC. if spts is an integer so that 9 is the same as saying = 10 then you could sort by a rounded version of spts like this... ORDER BY tpts DESC, ROUND(spts,-1) DESC, w DESC, ga ASC, team_number ASC by putting the -1 there it rounds to the nearest 10's before doing the sort. Of course the output is not rounded. The following are a few examples of the output of the round statement. ROUND(23.632, 2) = 23.63 ROUND(23.632, 1) = 23.6 ROUND(23.632, 0) = 24 ROUND(23.632, -1) = 20 -- 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: Sort Problem
On Sep 15, 2006, at 12:56 PM, Chris W wrote: Albert Padley wrote: I have the following query that has worked fine for displaying standings for a soccer league. SELECT * FROM standings WHERE division = 'BU10' AND pool = '1' ORDER BY tpts DESC, spts DESC, w DESC, ga ASC, team_number ASC As I said, works fine. Now, however, the league wants a slightly different sort order. They only want to sort on the spts column if the difference between 2 teams is greater than 9 in the spts column. All other sort criteria remain the same. So, the ORDER BY would be tpts DESC, spts DESC (but only if the difference is 9), w DESC, ga ASC, team_number ASC. if spts is an integer so that 9 is the same as saying = 10 then you could sort by a rounded version of spts like this... ORDER BY tpts DESC, ROUND(spts,-1) DESC, w DESC, ga ASC, team_number ASC by putting the -1 there it rounds to the nearest 10's before doing the sort. Of course the output is not rounded. The following are a few examples of the output of the round statement. ROUND(23.632, 2) = 23.63 ROUND(23.632, 1) = 23.6 ROUND(23.632, 0) = 24 ROUND(23.632, -1) = 20 -- Chris W KE5GIX Yes, that seems to work well. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query to get count of ages
I'm working on MySQL v5.0 and I have a table with dateOfBirth and I want a histogram of ages at a point in time. I tried something like: select round(datediff(curdate(), dateofbirth)/365) as age, count(age) from myTable group by age; but MySQL Query Browser says: Unknown column 'age' in 'field list' 1054 What am I doing wrong? How can I count ages? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to get count of ages
If your dateOfBirth is a date field, you can do this: select (substring(curdate(),1,4)-substring(dateofbirth,1,4))-(substr(curdate(),5)substr(dateofbirth,5)) as age from myTable; But you can't get a group by from an alias. Hope help. [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I'm working on MySQL v5.0 and I have a table with dateOfBirth and I want a histogram of ages at a point in time. I tried something like: select round(datediff(curdate(), dateofbirth)/365) as age, count(age) from myTable group by age; but MySQL Query Browser says: Unknown column 'age' in 'field list' 1054 What am I doing wrong? How can I count ages? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to get count of ages
You could try something like this: SELECT DISTINCT(round(datediff(curdate(), dateofbirth)/365)) as age, COUNT(round(datediff(curdate(), dateofbirth)/365)) AS total_age from myTable group by age Not sure, but could work. Regards, Alvaro João Cândido de Souza Neto escribió: If your dateOfBirth is a date field, you can do this: select (substring(curdate(),1,4)-substring(dateofbirth,1,4))-(substr(curdate(),5)substr(dateofbirth,5)) as age from myTable; But you can't get a group by from an alias. Hope help. [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I'm working on MySQL v5.0 and I have a table with dateOfBirth and I want a histogram of ages at a point in time. I tried something like: select round(datediff(curdate(), dateofbirth)/365) as age, count(age) from myTable group by age; but MySQL Query Browser says: Unknown column 'age' in 'field list' 1054 What am I doing wrong? How can I count ages? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
