Warning Re: My details [INF2003082300000269]
from what I can see from the subject and this message alone, someone on this list has been infected by the sobig.f virus, could everyone just please check their computers... that is if you are running any windows machine, not you that are running linux or any other os... thanks in advance... /Christian Andersson [EMAIL PROTECTED] wrote: Thanks for writing to [EMAIL PROTECTED] This is an auto-generated response to let you know that your message has reached our general information box on 8/23/2003 at 5:38:55 AM. Customer Service will be processing and, if necessary, responding to your inquiry shortly. We usually reply to general inquiries within one business day and appreciate your patience. There is no need to respond to this message, but if your message was concerning a technical support issue, a sales-related question, or beta product, there is a more direct and quicker way of contacting the appropriate SoftArtisans personnel--please see below. -- SoftArtisans Technical Support, Sales, and Beta Mailboxes -- Occasionally, our general information box receives messages that are more appropriate for another mailbox. For faster service, please consider resending your message directly to one of the follwing: For free technical support issues, please send your message directly to: [EMAIL PROTECTED] For a sales consultation, quote request, or other sales-related question, please send your message directly to: [EMAIL PROTECTED] For comments, feedback, or questions regarding our beta products, please send your message directly to: [EMAIL PROTECTED] If this is an urgent technical support problem and you require a guaranteed response time, SoftArtisans offers two levels of paid technical support: PRIORITY SUPPORT. Guaranteed response within two business hours of placing your call or sending your e-mail. There is no guarantee that we'll be able to provide an immediate resolution of your problem, but we will work on it until we can come to a conclusion. We will always provide you an expected date to issue you a patch if necessary. Please see instructions at: http://www.softartisans.com/softartisans/priorsup.html PER-INCIDENT SUPPORT. Guaranteed response within one business day of placing your call or sending your e-mail. There is no guarantee that we?ll be able to provide an immediate resolution of your problem, but we will work on it until we can come to a conclusion. If you would like to use a Per-incident technical support that was packaged with a product purchase (such as ASPStudio with Annual Maintenance), please call with your order number. Otherwise, please see instructions at: http://www.softartisans.com/softartisans/persup.html - Tracking Your General Information Request - Every incoming email is assigned a unique tracking number. Your tracking number is: [INF200308230269] In order to help us track the progress of request, please include [INF200308230269] in the subject line of any further mail regarding this issue. - SOFTARTISANS HOURS OF OPERATION - Monday through Friday, 9:00 a.m. to 5:00 p.m. Eastern Time (GMT-5) We look forward to reading your message and will respond as quickly as possible. Thanks. Sincerely, SoftArtisans Customer Service http://www.softartisans.com P.S. Please stay in touch! The SoftArtisans monthly newsletter is filled with helpful product information, upgrade notices and ASP tips. Please visit http://discuss.softartisans.com/cgi-bin/wa.exe?SUBED1=newsletter&A=1 if you would like to receive or stop receiving the newsletter. You are currently subscribed to the newsletter as [EMAIL PROTECTED] SoftArtisans respects your privacy and will not sell or rent personally identifiable information to anyone. Our privacy policy is posted at http://www.softartisans.com/softartisans/privacypolicy.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with windows XP and mysql
Hi there, I have a friend that is running mysql 4.10 gamma on his windows XP machine and he is having problem with an java application that uses jdbc to connect to mysql.. from what I coudl see it sometimes worked and it sometimes did not work... why I had no idea... but after digging somewhat in the code, I found en exception that was catched but not reported, the exception was as follows... java.sql.SQLEXception: Unable to connect to any hosts due to exception: java.net.ConnectException: Connection refused: connect at com.mysql.jdbc.Connection.createNewIO(Connection.java:1535) at ... huh, the application could not connect to the database? why? and why is this so inconsistent? sometimes he can connect and sometimes he cannot? the server and the application is running on the same computer, and he is using "localhost" to connect to... I have read something about all XP and how ports can be used (about 4000 free of them?) beeing used.. but would that not be a bindException then and not an connectException? And i hardly think that the computer has used over 4000 ports within 4 minutes.. I have searched this list, but not come to any conclusion any information at all would perheps be helpfull... Oh one more thing... now that 4.12 has been released.. how easy/hard is it to upgrade 4.10 gamma to 4.12 ? just stop the server, install the new version over the old, and start the server? /Christian Andersson - 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: creating update files
rent queries and therefore making the md5 calculating prove worthless? or are there other ways to get to the database structure that work the same on different platforms...? /Christian Andersson - 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
creating update files
Hi there, I have a little question for you in the mysql community.. I was wondering how persons in this community handles changes to the database when your application that uses the database needs some new tables, columns, etc... Do you have an application with a sort of upgrade functionallity that when started checks the database for incosistency and when it finds some updates the database? (ie compares the structure of the table in the database to some "template" stored in the application or as datafiles/scripts) Or do you use some "version" system where the application instead of looking for inconsistency just look at the version number of the database, compares it to some internal version, and updates the database accordingly? (ie manually creating all the needed updatescripts that needs to run the application and have the application select between these) Or perhaps you do this update of the database manually when intalling a new version? For me, version 1 would be preferable, but unfourtunally this does not work in every scenario, but i guess that it normally could take care of about 90% off all my changes... the second system would be able to take care of all type of changes, but it requires more labour... so, how do you all manage this? /Christian Andersson - 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
test
Since just before christmas I have not gotten any e-mails from the mysql-mailing list... so I send this to test my access, can anyone see it? Please reply in private so that I know if it works or not... /Christian Andersson - 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: Executing a \. or source function from an API
> ./mysql -uuser -ppassword database To: <[EMAIL PROTECTED]> Sent: Friday, December 21, 2001 11:56 AM Subject: Executing a \. or source function from an API > I have a big file full of ANSI SQL and a I want a script to be able to > tell Mysql to read this ANSI SQL file every few days because it updates > from a third party in ANSI SQL. I wish to avoid running a little parser > that just passes the SQL to the Perl-DBI module query methods, if I > could just simply tell Mysql to execute the SQL script file on the file > system. Any ideas? > > Thanks, > Curtis > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Nested Queries.
Subqueries are not (yet)working in mysql, they will be implemented in the 4.0.x or is it 4.x which hopefully will be out very soon :-) so if you are using the 3.23.xxx you have to stick with temporary tables /Christian - Original Message - From: "Larry Reiter" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, December 20, 2001 7:58 PM Subject: Nested Queries. > I have constructed a query which requires a temporary table but I > would rather use a nested query. In other words, I want to extract a > set of data and then use that set of data as a query against some > other data. Like some sort of SELECT within a SELECT. Can I do this > in MySQL? > > Cheers, > > LTR > -- > Dr. Lawrence T. Reiter > Postdoctoral Fellow > Department of Biology, UCSD > Bonner Hall Room 4221 > 9500 Gilman Drive > La Jolla, CA 92093-0349 > > Phone: (858) 534-0442 > FAX: (858) 822-2044 > http://homophila.sdsc.edu > email: [EMAIL PROTECTED] > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Sorting through results
Well there is one easy sollution (if I understand your qestion correctly)
What I think you want is an history of old properties and values..
so what to do? well create a "history" table that hold all these old values
and then the normal table only contains 1 value for each property..
So whenever an you are going to make an "update"
select the old values first and insert them into the history table, after
that
update the value in the normal table...
/Christian
- Original Message -
From: "P.Agenbag" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL PROTECTED]>
Sent: Wednesday, December 19, 2001 6:54 PM
Subject: Sorting through results
> HI
> This is possibly very easy, but I can't seem to get it right.
> I have a table that hold some data about properties. The data gets
> updated from time to time, but each time a new "update" is done, it
> get's a new row. The only thing in some cases setting the different
> entries appart is an "action_date" field I added that holds the date of
> the entry. The reason why I make a new entry is to keep a record of each
> time I receive data about a property for future reference. Now, my
> problem. I need to pull the data for a specific property (prop_name)
> that is a) not older than 52 weeks from the current date b) must have
> "success" in the "status" field, and c) must be the latest entry in case
> there are more than one entry for the same property matching all these
> criteria ( thus, I could have received a new entry within the 52 week
> period).
> So, how would the query string look that would only return ONE set of
> data for that specific property?
> A possible solution from a different source suggest the following, but
> I'm not sure it will work as it looks to me as if it still returns more
> than one row(possibly).
>
> $sql = "select * from table where prop_name='$prop_name' and
> prop_type='$prop_type' and status='success' and
> action_date>('".date("Y-m-d")."',INTERVAL 12 MONTH group by ID order by
> action_date desc)";
>
> PS, This is for a PHP page, but I guess it won't matter right?
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select 10 of each
hi there.. no solution from me, but I'm also interested in this type of query (although I only want 1 for each "lang") I also wants it in 1 query, not multiple (it would be to many queries) so any answer to this one please please reply to me to :-) although I do read here also... /Christian - Original Message - From: "Jacob Friis Larsen" <[EMAIL PROTECTED]> To: "Brett Error" <[EMAIL PROTECTED]>; "Mysql maillist" <[EMAIL PROTECTED]> Sent: Wednesday, December 19, 2001 8:11 PM Subject: RE: select 10 of each > Can't I put an IF / CASE in the query to make it work ? > > [ www.sharksforum.com | www.eksperten.dk | +45 70 27 07 67 ] > > > -Original Message- > > From: Brett Error [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, December 19, 2001 8:01 PM > > To: 'Jacob Friis Larsen'; Mysql maillist > > Subject: RE: select 10 of each > > > > > > You'll have to issue two separate queries: > > > > "select * from table where lang=1 limit 10" and "select * from table where > > lang=2 limit 10" > > > > -Original Message- > > From: Jacob Friis Larsen [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, December 19, 2001 11:46 AM > > To: Mysql maillist > > Subject: select 10 of each > > > > How do I select 10 rows with lang = 1 and 10 rows with lang = 2 > > from a MySQL > > database ? > > > > Please also reply to my E-Mail address. > > > > :) Jacob > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - 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, order by, temporary table
Hi there, I'm back with yet some questions about group by/order by in mysql.. My first question is how group by is beeing done if I have have columns in the select query that is not in the "group by" statement. Dring many test with 3.23-42 (Myisam tables) I found out that the content of the columns that is not in the was showing values for the first physical line in the database (withing the group criteria ofcourse) and here is my question... CAN I relly on the result that I have detected, if I can, is this table type dependent? will it change if I move from myIsam to innodb or some other table type? might this change to never versions of mysql? is this different depending on the operation system that mysql is running on? I have found out that by using this fact I can get some of my queries to work even better (I do not have to do that much logic programming in the program that does the query...) My second question is regarding temporary tables and order by , the answer to this affectsmy first question :-) if I make a query in mysql like this.. create temporary table test select * from real_table order by first_key, second_key; Will the rows be stored (physically) in the order that is stated in the order by statement? when the resulting temporary table is very small (lets say less then 100 rows) is it possiblefor mysql to cache the table completely in memory and when it is larger to savce it to disk(these temporary tables will only exist a very short time (create temp.., select from temp..., drop temp.) I really do not need them to be stored on the disc, unless they are to big and the result cannot be kept in memory.. Im asking this since I have a small problem that I have managed to solve with the above asked questions... I create a temporary table with the result from a join between 2 tables (head,lines) but since the resultcontaines to many lines (I only need 1 of them) and since I cannot use simple where to just select the line (trust me I cannot I have tried, perheps with) I use group by on the resulting temporary table. and finally drops the table... what I get is that I can "select" from which rows the data NOT in the group by statement will used as the resulting data.. one Oould say that I have by this created order by zzz group by yyy which in mysql does not exist (or in ansi sql either I guess) my other option would have been to do the join as usual, but not put the result in a temporary table, but retrieve it myself.. I would then programaticly have to create my "group by" and select the rows I want to..' with 3.23-42 on windows 2000 this actually works.. and it works very well One question to the mysql team (or any other mysql developer) since you allow columns in the query that does not exist in the group by, will you let us somehow decide which rows (from the ones grouped) the values will be taken from (like "order by zzz group by yyy" ) ? Well that was it for now from me :-) Merry X-mas and a Happy New Year! to all of you! /Christian - 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: NULL Strings
Well considering that you are searching for the text 'NULL' and not a null value haveyou tried " level2 is null" ? :-) - Original Message - From: "Ward, Mark" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, December 06, 2001 6:37 PM Subject: NULL Strings > I don't know how this affects anything, but, I'm running the most recent > version of mySQL for win98. > > Why is it that an SQL statement like this (or any variation)... > > SELECT * FROM classification > WHERE level1 = "FOO" AND level2 = "NULL"; > > will return an empty set. I know that there should be results. The NULL > value is screwing me up. Am I even referencing it properly? > > Mark Ward > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Execution Time Perl/DBD
The obvious way to do this would be to get the time before and after the execution and then make a simple diff (after - before) I guess that is how the mysql client does it (butI'm not sure) I do not think that mysql itself provides this information.. how to get the time in Perl/DBD I do not know. but in java it would be long tb = System.currentTimeMillis(); // do the sql-statement long diff = System.currentTimeMillis() - tb; - Original Message - From: "Gordan Bobic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, November 21, 2001 12:49 PM Subject: Query Execution Time Perl/DBD > Hi. > > How do I get the query execution time from Perl/DBD? MySQL client returns > this in the console window. Is this value available through the Perl DBD > interface? > > Thanks. > > Gordan > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Idea about extending Limit/mysql query
I'm not sure if this is a good idea or bad, but I have one idea about the
Limit funtionallity
(which incidently might solve my order by, group by problem :-)
I KNOW that it is NOT possible to do this now, so no RTFM :-) this is just
an idea..
LIMIT is as I understand it something that is mysql specific (I know
informix has something similiar but they write it some other way)
so adding functionallity here would not break any existing rule
anyway, my idea is this..
LIMIT columnname 1
would return only 1 row for each unique value in the columnname
LIMIT columnname1,columnname2 2
would return max 2 rows for each unique combination of values in the
columnname1+columnname2
or perheps have a different name for this..
Another way to solve my problem would be if there was some sort of
FIRST_IN_SET functionallity that I could use on the where clause
select * from articles where language = FIRST_IN_SET('en,sv,no')
remember these are just ideas, that might be scratched or remembered
If I know how to do it, I would perheps implement this myself in my own
version of mysql, but I'm not THAT good .-)
/Christian Andersson
-
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: order by, group by
ll getting a List of articles for example (that displays only the heading, or the name, etc) > =what is your 'prime' language - or better, what is the order of language preference (from most prefered to > least-used) Well that depends on the user, for me it would be swedish, english, norwegian, for a partner of mine it would be norwegian, swedish, english.. I am after all not doing this program for me, but for the users, and the users in this case will be very international! > =it's getting too complicated for me - but someone who's good at this stuff might be able to solve it for you > without change. > > =At this point I'm wondering if it would be easier to act cowardly and run away? How about changing the language > column to a digit. Select which language is represented by which digit according to your language preference. > Then the ASC | DESC will work cheerfully/easily! changeing the language column to a digit will not help since the sorting would then have to be first #7 then #2 then #4 then all the rest.. no big difference, besides it is a hell lot easier to debug when it says language='en' and not language=34 for instance :-) I have found out that with the version running here now, the temporary table sollution works (observer, the below is not TRUE sql) create temporary table tmp_articles(id,language,name,description,languagorder); insert into tmp_articles (id,language,name,description,languagorder) select *,FIND_IN_SET(language,'en,fr,no') languageorder from articles order by languageorder desc; select * from tmp_articles group by id; will do what I want, however if the temporary table:s rows are not retrieved inthe same order that they were inserted, or if the insert is not donw ordered or if the group by does not take the first row it finds for each group... then I'm at loss but so far, all is allright, hopefully (if there is anyone reading this by now :-) someone out there knows how rows are inserted, retrieved from temporary tables and how group by works.. but I'd still would like to do this in a single query (order by before group by perhepes? :- /Christian Andersson - 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: order by, group by
Thank you for your responce, you have guessed it correctly the only difference is the usage of MAX which I cannot use (see my explenation on what I want to do in a seperate message) I'm not sure IF I can do it with sub-selects either, but when subselects is present in mysl, I could try that one.. ( do not have any other database to test with, so I cannot try it out) but thanks anyway.. - Original Message - From: "Carl Troein" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 13, 2001 1:23 PM Subject: Re: order by, group by > > DL Neil writes: > > > "All keywords used must be given in exactly the order shown above. For example, a HAVING clause must come after > > any GROUP BY clause and before any ORDER BY clause." > > As far as I understood the original post, the question was if > there's a way to get MySQL to perform an ORDER BY prior to > doing the GROUP BY. In ANSI SQL this would be pointless, since > you have to group by all returned fields that are not 'functions > for use with group by'. In MySQL you can do something like > SELECT a,b FROM table1 GROUP BY a > to get the value of b for an arbitrary row for each a. > This is mostly useful when you want a large number of columns > returned and you know that whatever you're grouping by is > unique, i.e. COUNT(*) would be 1 for every returned row. > > However, it would sometimes be useful to be able to extract the > top value of something for each value of something else, _along > with additional information about that top value_. For instance, > you might want to know the top score for every week's quiz, and > along with that the name of the best student. This would typically > be done with a subselect, along the lines of > SELECT date,score,name FROM quiz WHERE (date,score) IN (SELECT > date,MAX(score) FROM quiz GROUP BY date) > but this will give you more than one row per date if several > people have the same score that day. > I think what the poster was looking for was a way to do > this with something like (and this is very broken): > SELECT date,MAX(score),(name with score=MAX(score)) FROM quiz > GROUP BY date > which could be written as something like > SELECT date,MAX(score),name FROM quiz ORDER BY score > GROUP BY date ORDER BY date > since you want to pick the name that matches the highest score. > > Unfortunately there is no way to do this in MySQL right now. > You will have to first > SELECT date,MAX(score) FROM quiz GROUP BY date > and then loop over the result and > SELECT name FROM quiz WHERE date=... ORDER BY SCORE DESC LIMIT 1 > > Once MySQL gets subselect you'll probably be able to do it like > SELECT date,score,name FROM quiz WHERE (date,score) IN (SELECT > date,MAX(score) FROM quiz GROUP BY date) GROUP BY date,score > which would return an arbitrary person for each date's top score. > > I hope this makes sense and clears things up. > > //C > > -- > Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 > [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ > Amiga user since '89, and damned proud of it too. - 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
order by, group by
I asked before, but got no answer, so I ask again, but refrase myself a little Is it possible in myslq to do an order by before an group by? as far as I know, the order by will happen after the group by, and sort all the grouped rows(I assume that when grouping it just takes the first found unique row), but what I'd really would like to do is sort the results before grouping so that I can controle which rows are to be presented back to me... the only way right now for me is to skip the group by in mysql, and do an own group by in the program, but I guess that if it is possible to do in mysql it would be faster since there would atleast be less to transfer from mysql to my application (java/jdbc based) or are there other tricks I can use to get what I want? /Christian Andersson - 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
sql query help
I know, I know... this is not really an MySQL question but an ordinary SQL question.. but I need help with it.. Lets say that I have a table called test that has 3 columns test1,test2,test3 (what type they are are not interesting) columns test1 and test2 makes the primary key what I would like is something like the following select *,FIND_IN_SET(test2,'9,5,7') test2order from test where test1=1 and PERIOD_ID=1 order by test2order DESC (values in column test2 should be ordered by 9,5,7,all the rest) but I would ONLY want the first row of the keys (test1,test2) ie if it finds a 9 it would return that row but not any other row if it doesnot find a row with value 9 it should return the one with value 5, etc... so I thought that by doing a group by would sort this (grouping by columns test1,test2) select *,FIND_IN_SET(test2,'9,5,7') as test2order from test where test1=1 and PERIOD_ID=1 group by test1,test2 order by test2order DESC I do get 1 row, but not the row I want.. since unfourtunally group by is "executed" before order by, and I will not get the list in the order I want it IE I want the query sorted BEFORE the group by I have also tried doing it like this select *,FIND_IN_SET(test2,'9,5,7') test2order from test where test1=1 and PERIOD_ID=1 and test2order=max(test2order) order by test2order DESC but that will not work either is there any way to do this in sql? in mysql? - 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: Connection timeout and pooling
if you intend to use mysql you could send down for instance "select 1" to the database.. but this is not database independent since not all databases might be albe to handle this type of query but it is application independent when using mysql. if you plan to use an other database some other form of sql query might be needed and now it might also be application dependant.. the other way to do it is to assume that after a while a connection will die and create a new connection every timeyou make an query to the database you store the timefor the connection (last used time) and every time you get a connection from the pool the time is checked against a timeout, if the current time - last used time is larger then the timeout, "close" the connection and make a new connection since I can find no standard "keep alive" system in jdbc (I'm using java) or sql/database I myself had to go for this last sollution.. hope it helps! /Christian Andersson - Original Message - From: "Sylvain Hamel" <[EMAIL PROTECTED]> To: "MySQL Mailing list" <[EMAIL PROTECTED]> Sent: Friday, November 09, 2001 3:50 PM Subject: Connection timeout and pooling > IDE : JBuilder 4 prof > > I am using pooling in order to optimize database access. When everybody > leaves the office for the weekend all my connection timeout. This is > expected except I would like my connection pool to be able to test > connections before passing them back to the requester. Whats the best way to > do this? Cant say I like sending a (context dependent) query to the database > because it makes my class not re-usable across applications. - 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: java.util.Date, jdbc, and DATETIME
> This question was asked back in 1999 and wasn't answered, so hopefully > someone will answer it now. > > I am using JBoss and they provide a JDBC to mySQL type mapping that maps > java.util.Dates to a jdbc-type of DATE and mySQL type of DATETIME. Well, > only the date portion (10-31-2001) is being stored and the time is set to > 00:00:00. It is a very simple question, why isn't the time being stored? I > am using the MM jdbc driver. Is it the driver code? The reason could be one of 2 possible. The type of the column,and how you set the date.. since you say the column is of type DATETIME that is notthe problem. If you are using prepared statements in jdbc then the problem couldbe that you are using the function stmt.setDate(column, date); try use stmt.setTimestamp(column, date) instead.. since setDate is for setting dates, not date AND time :-) other jdbc:s this might set a datetime also, but not the mm.mysql jdbc.. if this is a bugor just a stricter handlingI do not know.. but that is most likely your problem /Christian Andersson - 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
looping
Hi there, I have a small question for you all (and yes I have searched the documents but not be able to found what I want) Lets say that I have a table with these values 1, 5 and 7 is it possible to create an sql query that will "loop" through these values a certain amount of time for example if i wanted 8 rows I would get 1, 5, 7, 1, 5, 7, 1, 5 as output.. I'm currently doing this looping in my application but it would speed things up if mysql could do this for me.. since I could then do some joins and retrieve values from other tables faster. /Christian Andersson - 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
Linux mysql problems....
Hi there.. I'm not sure if anyone can help me with this, but I have just experienced a very disturbing situation with mysql. Today when coming to work, one of our databases in mysql seems to have lost a lot of data, it was as if the database was restored from a backup several days ago.. but only for some tables not all of them. and some tables had also lost data that was very old, but retained data that was newer... I had a backup script that was running every night that was not working as I thought it should have been working.. The Backup script (borrowed from the net and slightly modified) does a FULL permament backup the first every month. then a FULL backup every sunday (that gets overwritten every sunday) and then a backup every day for file sthat have been changed (the date/time for the file is newer...) since last sunday The backups just does a TAR on the files in the data folder... The Full backup from sunday was OK and worked so I had to restore it from that date.. but the daily backup that should take files that have changed did not backup the tables(files) that I had inserted new rows in.. either the backup script is in error, tar did not see that the file was changed OR (and this is the worrying part) mysql/linux had not updated the files timestamp so that TAR did not see that they vere modified.. I have now changed the backup script so that instead of copying the files I use mysqldump for backup, and does a FULL backup every night (with mysqldump) Does anyone have any recomendations on how to backup and when? Anyway back to the original problem.. The database had lost a lot of data.. Searching for an error, the only thing I found out was that sometime in the middle of the night, the database had been restarted (I have a script that checks and sees if mysql exist every 5 minutes, and if it does not, I start it) and just before the restart (why it had restarted I cannot find out) there were 105 mysql-servers running (1 per connection + some spare?) (Max_c onnection was configured to 100) could this be the problem? ie that mysql had "shutdown/crashed" while max number of connections was reached (I'm not saying that it crashed/shutdown because of this) I'm not that familiar with the whole concept of mysql/linux and there are some scripts that are running in this setup that I have not checked yet (I did not configure this linux, but was "forced into it) I'm running Ver 3.23.42 for pc-linux-gnu on i686 under SuSE 7.2 I have changed a little in how connections are made to the database so hopefully it will never max out again and I've incresed the max_connection to 1000 connections.. is there anything more I should do? any other recommendations to get a healthy system? (I'm using jdbc to access mysql (mm.mysql.) can that be a problem? (I'm going to add debugging that will write down every update to the database to a seperate file also... just so that IF tis happen again I CAN recreate the data even if the backup is not working...) If you ahve any ideas on these subjects I'd be glad to hear it! /Christian Andersson - 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
