Group by with an IF
I have the following query: select *, if( season_week_date = 2006-08-16, on, off ) as stat, sum(overall_points) as total_points from rosters r left join celebs c on c.celeb_id = r.celeb_id where season_id=5062 and user_id=1 group by r.celeb_id order by overall_rank, ln, fn; It almost works as expected however the problem I am concerned about is that I never get a stat saying On after it resolves. Removing the sum() and adding group I get the total_points as expected but now the results are always off. Can anyone shed any light on this one? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
logging
Hello, I realize that this is a silly question, but I cannot figure it out. I don't know why. Id really appreciate your help. I cannot get mysql to do general logging. My my.ini reads like this: -- #This File was made using the WinMySQLAdmin 1.4 Tool #8/4/2006 9:31:16 PM #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] log=C:/development/xampp/mysql/data/hostname.log basedir=C:/development/xampp/mysql #bind-address=192.168.1.222 datadir=C:/development/xampp/mysql/data #language=C:/XAMPP/xampp/mysql/share/your language directory #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M [WinMySQLadmin] Server=C:/development/xampp/mysql/bin/mysqld-nt.exe user=root password= -- I start the service, no hostname.log is created. Even if I don't run it as a service, it does not work. Permissions are set... this is my machine at home and I am running as Admin. Even if I create the file manually, it doesn't get updated. For one second, unexplicably, it DID log one line. Then it stopped and did no more. This is happening here at home and at work... and I need to be able to see the sql being executed on my server. The error log is working fine. Any ideas? I sure would appreciate it. -Original Message- From: Steffan A. Cline [mailto:[EMAIL PROTECTED] Sent: Sunday, August 13, 2006 5:34 AM To: mysql@lists.mysql.com Subject: Group by with an IF I have the following query: select *, if( season_week_date = 2006-08-16, on, off ) as stat, sum(overall_points) as total_points from rosters r left join celebs c on c.celeb_id = r.celeb_id where season_id=5062 and user_id=1 group by r.celeb_id order by overall_rank, ln, fn; It almost works as expected however the problem I am concerned about is that I never get a stat saying On after it resolves. Removing the sum() and adding group I get the total_points as expected but now the results are always off. Can anyone shed any light on this one? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- 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: Do I really need a subquery?
Geoffrey, Do I really need that subquery? You need its scalar result just once, so why not ... SELECT url, @maxcount:=COUNT(1) FROM bi_bookmarks WHERE date NOW() - INTERVAL 86400 SECOND GROUP BY url ORDER BY count DESC LIMIT 0, 1; SELECT title, url, COUNT(1) AS count, CEIL(COUNT(1) / (@maxcount * 8) AS weight FROM bi_bookmarks WHERE date NOW() - INTERVAL 86400 SECOND GROUP BY url ORDER BY count DESC LIMIT 0, 10; PB - Geoffrey Sneddon wrote: Hi, I've ended up with the following SQL: SELECT `title`, `url`, COUNT(1) AS `count`, CEIL(COUNT(1) / (SELECT COUNT(1) AS `count` FROM `bi_bookmarks` WHERE `date` NOW() - INTERVAL 86400 SECOND GROUP BY `url` ORDER BY `count` DESC LIMIT 0, 1) * 8) AS `weight` FROM `bi_bookmarks` WHERE `date` NOW() - INTERVAL 86400 SECOND GROUP BY `url` ORDER BY `count` DESC LIMIT 0, 10; Do I really need that subquery? It seems rather pointless having similar queries like that. Any other optimisations tips are of course welcome (just to note, there are reasons for having the interval in seconds). - Geoffrey Sneddon --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.10.9/417 - Release Date: 8/11/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.10.9/417 - Release Date: 8/11/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql++ problem(undefined symbol to)
hello. i want to connect to mysql from c++ . i examine any version or platform. i use mysql5 and 4 in suse10 with gcc.then examine mingw and mysql5 and 4 and 3 for severeal times but i can't solve this problem.when i want to compile examples of mysql++ following error appeared: ** custom1.o(.text+0x180):custom1.cpp: undefined reference to `_imp___ZN7mysqlpp10C onnectionC1Eb' custom1.o(.text+0x1ba):custom1.cpp: undefined reference to `connect_to_db(int, c har**, mysqlpp::Connection, char const*)' custom1.o(.text+0x1d6):custom1.cpp: undefined reference to `mysqlpp::Connection: :~Connection()' custom1.o(.text+0x1fd):custom1.cpp: undefined reference to `_imp___ZN7mysqlpp10C onnection5queryEv' custom1.o(.text+0x2ef):custom1.cpp: undefined reference to `print_stock_header(i nt)' custom1.o(.text+0x3fe):custom1.cpp: undefined reference to `print_stock_row(std: :string const, long long, double, double, mysqlpp::Date const)' custom1.o(.text+0x509):custom1.cpp: undefined reference to `mysqlpp::Connection: :~Connection()' custom1.o(.text+0x53b):custom1.cpp: undefined reference to `mysqlpp::Connection: :~Connection()' custom1.o(.text$_ZN7mysqlpp5QueryD1Ev[mysqlpp::Query::~Query()]+0x39):custom1.cp p: undefined reference to `vtable for mysqlpp::Query' custom1.o(.text$_ZN7mysqlpp5QueryD1Ev[mysqlpp::Query::~Query()]+0x48):custom1.cp p: undefined reference to `vtable for mysqlpp::Query' custom1.o(.text$_ZN7mysqlpp5QueryD1Ev[mysqlpp::Query::~Query()]+0x57):custom1.cp p: undefined reference to `vtable for mysqlpp::Query' custom1.o(.text$_ZN7mysqlpp5QueryD1Ev[mysqlpp::Query::~Query()]+0x63):custom1.cp p: undefined reference to `vtable for mysqlpp::Query' custom1.o(.text$_ZN7mysqlpp5QueryD1Ev[mysqlpp::Query::~Query()]+0x21a):custom1.c pp: undefined reference to `VTT for mysqlpp::Query' custom1.o(.text$_ZN7mysqlpp5QueryD1Ev[mysqlpp::Query::~Query()]+0x240):custom1.c pp: undefined reference to `VTT for mysqlpp::Query' custom1.o(.text$_ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQ LQueryParmsENS_11query_resetE[void mysqlpp::Query::storeinstd::vectorstock, st d::allocatorstock (std::vectorstock, std::allocatorstock , mysqlpp::SQ LQueryParms, mysqlpp::query_reset)]+0x71):custom1.cpp: undefined reference to ` _imp___ZN7mysqlpp5Query3strERNS_13SQLQueryParmsENS_11query_resetE' custom1.o(.text$_ZN7mysqlpp4DateD1Ev[mysqlpp::Date::~Date()]+0xb):custom1.cpp: u ndefined reference to `vtable for mysqlpp::Date' custom1.o(.text$_ZN7mysqlpp5Query16storein_sequenceISt6vectorI5stockSaIS3_vR T_PKc[void mysqlpp::Query::storein_sequencestd::vectorstock, std::allocatorst ock (std::vectorstock, std::allocatorstock , char const*)]+0x59):custom 1.cpp: undefined reference to `_imp___ZN7mysqlpp5Query3useEPKc' custom1.o(.text$_ZN7mysqlpp5Query16storein_sequenceISt6vectorI5stockSaIS3_vR T_PKc[void mysqlpp::Query::storein_sequencestd::vectorstock, std::allocatorst ock (std::vectorstock, std::allocatorstock , char const*)]+0x85):custom 1.cpp: undefined reference to [EMAIL PROTECTED]' custom1.o(.text$_ZN7mysqlpp5Query16storein_sequenceISt6vectorI5stockSaIS3_vR T_PKc[void mysqlpp::Query::storein_sequencestd::vectorstock, std::allocatorst ock (std::vectorstock, std::allocatorstock , char const*)]+0xb3):custom 1.cpp: undefined reference to [EMAIL PROTECTED]' custom1.o(.text$_ZN7mysqlpp5Query16storein_sequenceISt6vectorI5stockSaIS3_vR T_PKc[void mysqlpp::Query::storein_sequencestd::vectorstock, std::allocatorst ock (std::vectorstock, std::allocatorstock , char const*)]+0xde):custom 1.cpp: undefined reference to `_imp___ZN7mysqlpp3RowC1ERKPPcPKNS_6ResUseEPmb' custom1.o(.text$_ZN7mysqlpp5Query16storein_sequenceISt6vectorI5stockSaIS3_vR T_PKc[void mysqlpp::Query::storein_sequencestd::vectorstock, std::allocatorst ock (std::vectorstock, std::allocatorstock , char const*)]+0x114):custo m1.cpp: undefined reference to `mysqlpp::Row::~Row()' custom1.o(.text$_ZN7mysqlpp5Query16storein_sequenceISt6vectorI5stockSaIS3_vR T_PKc[void mysqlpp::Query::storein_sequencestd::vectorstock, std::allocatorst ock (std::vectorstock, std::allocatorstock , char const*)]+0x1fd):custo m1.cpp: undefined reference to `mysqlpp::Row::~Row()' custom1.o(.text$_ZN7mysqlpp5Query16storein_sequenceISt6vectorI5stockSaIS3_vR T_PKc[void mysqlpp::Query::storein_sequencestd::vectorstock, std::allocatorst ock (std::vectorstock, std::allocatorstock , char const*)]+0x223):custo m1.cpp: undefined reference to `mysqlpp::Row::~Row()' custom1.o(.text$_ZN7mysqlpp5Query16storein_sequenceISt6vectorI5stockSaIS3_vR T_PKc[void mysqlpp::Query::storein_sequencestd::vectorstock, std::allocatorst ock (std::vectorstock, std::allocatorstock , char const*)]+0x249):custo m1.cpp: undefined reference to `mysqlpp::ResUse::~ResUse()'
Finding the last row that has
Hi, I have one table with images (image_id, name, filename, vote_count) and one table with votes (vote_id, image_id, vote_value, user_id). I want to find the image with the lowest vote_count that a known user has not yet voted. For MySQL 4.0 I have the following: SELECT i.* FROM images i LEFT JOIN votes v ON i.image_id = v.image_id WHERE v.id IS NULL OR v.user_id != '1234' GROUP BY image_id ORDER BY vote_count ASC LIMIT 1; Now my three questions: Is this the optimal query? Is there a better query when using MySQL 4.1? Since it's redundant, can I get rid of the vote_count column? Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joining result sets into 1 row
I am in a situation where I have say 1 column called attribute I need and the result set is 3 rows. i.e. ROW 1 - Mechanic ROW 2 - Carpenter ROW 3 - Plumber I want to have the rows returned as one row Such as ROW 1 Mechanic, Carpenter, Plumber Something like a literal join would be beautiful such as : ROW 1 Mechanic, Carpenter and Plumber I think the latter is asking for too much but the first would be awesome. Any advice is much appreciated! Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining result sets into 1 row
On Sun, 2006-08-13 at 15:53 -0700, Steffan A. Cline wrote: I am in a situation where I have say 1 column called attribute I need and the result set is 3 rows. i.e. ROW 1 - Mechanic ROW 2 - Carpenter ROW 3 - Plumber I want to have the rows returned as one row Such as ROW 1 Mechanic, Carpenter, Plumber Something like a literal join would be beautiful such as : ROW 1 Mechanic, Carpenter and Plumber Not sure how your row are. is it really marked as Row1/Row2/Row3? If yes, you can use a case expression. select case when row=row1 then row else null end as mechanic , case when row=row2 then row else null end as carpenter, end -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
random sort?
so if i have a list of records... id name other 1 water H2O 2 water aqua 3 water liquid so i want to select other where name = 'water' but i want to only return 1 result, and for that result to be a random value. is there a way to sort by rand() ? or something similar? or am i better of just returning the entire set and writing a function to pick a random value?
Re: random sort?
Tanner Postert wrote: so if i have a list of records... id name other 1 water H2O 2 water aqua 3 water liquid so i want to select other where name = 'water' but i want to only return 1 result, and for that result to be a random value. is there a way to sort by rand() ? Yes. Funnily enough: select * from table order by rand(); If you only want one result: select * from table order by rand() limit 1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: non-text data
Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I use SELECT all from * ... and one row is a gif. How do I get that gif to appear as a gif and not text? In your connecting programming language. Mysql doesn't know or care whether it's a gif, pdf, word doc or anything else. Whatever programming language you are using to connect to mysql and fetch the data will be able to convert that binary data and display an image. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems with sql-mysql mode of emacs on Windows
Jieqi Wang wrote: I am using WinXP, GNU Emacs 23, MySQL 5. When I start a mysql process with `sql-mysql' in emacs, no output is displayed in the buffer until I quit or kill the process. After I add the following line into the initialization file of emacs, (setq sql-mysql-options '(-C -t -f -n)) the output comes out, but the status line still doesn't show up. (e.g. 1 row in set (0.02 sec)) What's worse, I try to log the session with `\T logfile', but no logfile is generated. ( I check that with Google Desktop) You're better off asking an emacs list. It's not really a mysql issue. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changed?
MYSQL wrote: Hi, I recently updated to version 4.1.2 from a slightly older one like 4.1.11 or something. But I am now having a problem. Before,this query would work fine: Select distinctrow * from mytable order by mydatecolumn. mydatecolumn is a column of type date. This used to return the records in order of date, as it should. After the update however, it now returns them in random order. If i use a int type column to order by, then it works fine, it seems ony the date columns will no longer work. If i take distinctrow out of the query then it should work, but i have way to much to change to do that, and i still need to be able to select distinct rows when making a join. I highly doubt that's the case otherwise mysql.com would have been flooded with bug reports about this. Can you provide a sample case of what's going on (create table syntax, 5-10 lines of data and the exact query you're running) and someone might be able to work out what's going on. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
silly trigger question
Hi, Would something like this make it so that every time there was a new row inserted on TEST, a row with the new values was inserted on LOG_TABLE? I am not sure if you can reference NEW.id and all the NEW values directly and send them in a trigger... Thanks... CREATE TRIGGER test.data_table_au AFTER UPDATE ON test.data_table FOR EACH ROW BEGIN INSERT INTO log_table (id_data, old_d1, new_d1, old_d2, new_d2, kind_of_change, ts) VALUES (new.id, old.d1, new.d1, old.d2, new.d2, 'update', now()); END And could I go ahead and update two tables in the same trigger or am I better off making the second trigger after update on the second table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random SELECT on subset
Dear MySQL-ers, Using MySQL 4.1.20, I'm trying to do a complex query on a subset; well, complex to me, that is. :) In Perl, I'm trying to get 4 random entries from a subset WHERE processed = '1' and columnId is unique. Like so: $sth = $dbh-prepare (SELECT columnId FROM queue WHERE columnId = (SELECT FLOOR(MAX(columnId) * RAND()) FROM queue) IN (SELECT columnId FROM queue WHERE processed = '1') ORDER BY columnId LIMIT 4); Seems to work fine. Only problem is, every once in a while I only get 3 numbers returned (or none at all, when I set LIMIT 1). Something to do with FLOOR and RAND, I reckon; but if I knew exactly what the problem was, I wouldn't asking. :) So, anyone here know what I'm doing wrong in my query? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random SELECT on subset
Mark wrote: Dear MySQL-ers, Using MySQL 4.1.20, I'm trying to do a complex query on a subset; well, complex to me, that is. :) In Perl, I'm trying to get 4 random entries from a subset WHERE processed = '1' and columnId is unique. Like so: $sth = $dbh-prepare (SELECT columnId FROM queue WHERE columnId = (SELECT FLOOR(MAX(columnId) * RAND()) FROM queue) IN (SELECT columnId FROM queue WHERE processed = '1') ORDER BY columnId LIMIT 4); I'm not even sure what you're trying to get here! Could you provide some sample data (5 rows) and what you want the query to return? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Random SELECT on subset
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 3:55 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset In Perl, I'm trying to get 4 random entries from a subset WHERE processed = '1' and columnId is unique. Like so: $sth = $dbh-prepare (SELECT columnId FROM queue WHERE columnId = (SELECT FLOOR(MAX(columnId) * RAND()) FROM queue) IN (SELECT columnId FROM queue WHERE processed = '1') ORDER BY columnId LIMIT 4); I'm not even sure what you're trying to get here! Could you provide some sample data (5 rows) and what you want the query to return? Well, there are a great many colums in the real rows, but say they are like this: columnID, picturename, processed, 1 name11 2 name20 3 name31 4 name41 5 name51 6 name60 7 name71 Then I want to select 4 random columnIDs, but only from the subset WHERE processed = '1' (so, from the group 1,3,4,5,7). Thanks. I appreciate your help. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random SELECT on subset
Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 3:55 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset In Perl, I'm trying to get 4 random entries from a subset WHERE processed = '1' and columnId is unique. Like so: $sth = $dbh-prepare (SELECT columnId FROM queue WHERE columnId = (SELECT FLOOR(MAX(columnId) * RAND()) FROM queue) IN (SELECT columnId FROM queue WHERE processed = '1') ORDER BY columnId LIMIT 4); I'm not even sure what you're trying to get here! Could you provide some sample data (5 rows) and what you want the query to return? Well, there are a great many colums in the real rows, but say they are like this: columnID, picturename, processed, 1 name11 2 name20 3 name31 4 name41 5 name51 6 name60 7 name71 Then I want to select 4 random columnIDs, but only from the subset WHERE processed = '1' (so, from the group 1,3,4,5,7). This query should do what you want: SELECT columnID from table where process='1' order by rand() limit 4; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Random SELECT on subset
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 4:18 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Then I want to select 4 random columnIDs, but only from the subset WHERE processed = '1' (so, from the group 1,3,4,5,7). This query should do what you want: SELECT columnID from table where process='1' order by rand() limit 4; What? And all the FLOOR stuff can go? :) That sounds too simple, lol. Well, thanks anyway; I'll go try it. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection Help - Stupid Question, sorry to bother.
Chris W wrote: By default MySQL uses port 3306 so you need to be sure that port is open on the server, and not blocked by a firewall. You also need to be sure the user you are trying to login as can login remotely. In the MySQL user data base, there is a column for host which is the host that user can login from. If that host says localhost you can only login from the localhost. If it says % you can login from any host. Also note there can be more than one entry for each user all with a different host. It is best to only set it up so you can login from a specific host, that makes it more difficult for a hacker to break in. If the user you are logging in as is set up just for localhost I would add a user and use the host you plan to login from if you can, other wise just change the host to % then you can login from anywhere. Also if you do an update to the user table, using the sql update command, you also need to execute flush privileges for the changes to take effect. The other thing is that mysql has to be set up to allow remote connections, it's not by default on some systems (eg debian). Check the my.cnf and make sure there is no 'skip-networking'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group by with an IF
Steffan A. Cline wrote: I have the following query: select *, if( season_week_date = 2006-08-16, on, off ) as stat, sum(overall_points) as total_points from rosters r left join celebs c on c.celeb_id = r.celeb_id where season_id=5062 and user_id=1 group by r.celeb_id order by overall_rank, ln, fn; It almost works as expected however the problem I am concerned about is that I never get a stat saying On after it resolves. Removing the sum() and adding group I get the total_points as expected but now the results are always off. Remove the if first and make sure the rest of the query returns the results you expect. That will tell you either the query isn't working the way you want, or the if check isn't working. Is season_week_date only a date or is it a date/time field? If the query isn't working the way you want, can you post relevant table schemas and 5 rows of data from each and what you expect to get? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql++ problem(undefined symbol to)
ali asghar torabi parizy wrote: hello. i want to connect to mysql from c++ . i examine any version or platform. i use mysql5 and 4 in suse10 with gcc.then examine mingw and mysql5 and 4 and 3 for severeal times but i can't solve this problem.when i want to compile examples of mysql++ following error appeared: It would be better to ask on the mysql++ list. http://lists.mysql.com/plusplus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding the last row that has
André Hänsel wrote: Hi, I have one table with images (image_id, name, filename, vote_count) and one table with votes (vote_id, image_id, vote_value, user_id). I want to find the image with the lowest vote_count that a known user has not yet voted. For MySQL 4.0 I have the following: SELECT i.* FROM images i LEFT JOIN votes v ON i.image_id = v.image_id WHERE v.id IS NULL OR v.user_id != '1234' GROUP BY image_id ORDER BY vote_count ASC LIMIT 1; Now my three questions: Is this the optimal query? That query should actually produce an error because the group by image_id is ambiguous - it's in both tables and you're not qualifying it. Anyway, do you have an index on i.image_id and v.image_id ? That will make sure the join is reasonably fast. Is there a better query when using MySQL 4.1? Not really, mysql5 has subqueries but mysql4.1 doesn't. Since it's redundant, can I get rid of the vote_count column? Why is it redundant? You said you need the one with the lowest count that the user hasn't voted on. (I could also be mis-reading your question). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random SELECT on subset
Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 4:18 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Then I want to select 4 random columnIDs, but only from the subset WHERE processed = '1' (so, from the group 1,3,4,5,7). This query should do what you want: SELECT columnID from table where process='1' order by rand() limit 4; What? And all the FLOOR stuff can go? :) That sounds too simple, lol. (just noticed a typo, my query should be where processed='1' not process='1').. Based on what you've said that should be the whole query yes :) Of course make sure it provides the information you want.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: non-text data
On Mon, Aug 14, 2006 at 10:42:50AM +1000, Chris wrote: Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I use SELECT all from * ... and one row is a gif. How do I get that gif to appear as a gif and not text? In your connecting programming language. Mysql doesn't know or care whether it's a gif, pdf, word doc or anything else. Whatever programming language you are using to connect to mysql and fetch the data will be able to convert that binary data and display an image. PHP? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query needed
Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss regards, venu.
RE: Random SELECT on subset
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 4:54 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Hmm, this still does not do what I want: SELECT columnId FROM queue WHERE processed = '1' ORDER BY RAND() LIMIT 4 Thanks to you, the query has been greatly simplified, but the result is still the same: every once in a while (like if I run this twenty times in a row), I only get 3 items returned, or even 2! I'm guessing the RAND() function occassionally rounds the number to a columnId (bigint) that does not match the subset of the WHERE clause? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query needed
VenuGopal Papasani wrote: Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss sure. select c1 + c2 - c4 - c5; doesn't help you fix the problem because you haven't told us what the criteria is for the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: non-text data
Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: On Mon, Aug 14, 2006 at 10:42:50AM +1000, Chris wrote: Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I use SELECT all from * ... and one row is a gif. How do I get that gif to appear as a gif and not text? In your connecting programming language. Mysql doesn't know or care whether it's a gif, pdf, word doc or anything else. Whatever programming language you are using to connect to mysql and fetch the data will be able to convert that binary data and display an image. PHP? http://www.php.net/gd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random SELECT on subset
Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 4:54 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Hmm, this still does not do what I want: SELECT columnId FROM queue WHERE processed = '1' ORDER BY RAND() LIMIT 4 Thanks to you, the query has been greatly simplified, but the result is still the same: every once in a while (like if I run this twenty times in a row), I only get 3 items returned, or even 2! And how many have 'processed=1' at that stage? I highly doubt it's the rand() doing it - it's your data changing. If you only have 2 unprocessed items in the queue, then mysql can only ever retrieve 2 results no matter what limit you put on it. The WHERE part of the query is processed before the limit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Random SELECT on subset
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 6:32 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Thanks to you, the query has been greatly simplified, but the result is still the same: every once in a while (like if I run this twenty times in a row), I only get 3 items returned, or even 2! And how many have 'processed=1' at that stage? I highly doubt it's the rand() doing it - it's your data changing. If you only have 2 unprocessed items in the queue, then mysql can only ever retrieve 2 results no matter what limit you put on it. The WHERE part of the query is processed before the limit. The data is NOT changing, of course. :) Just a small, fixed test table and a test.pl prog to run this off a prompt. Same table, same test prog, ran several times in a row. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random SELECT on subset
Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 6:32 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Thanks to you, the query has been greatly simplified, but the result is still the same: every once in a while (like if I run this twenty times in a row), I only get 3 items returned, or even 2! And how many have 'processed=1' at that stage? I highly doubt it's the rand() doing it - it's your data changing. If you only have 2 unprocessed items in the queue, then mysql can only ever retrieve 2 results no matter what limit you put on it. The WHERE part of the query is processed before the limit. The data is NOT changing, of course. :) Just a small, fixed test table and a test.pl prog to run this off a prompt. Same table, same test prog, ran several times in a row. Easiest way to tell is add an extra query: select count(*) from tablename where processed='1'; ..or even: select * from tablename where processed='1'; so you can compare what the rand() is doing compared to the table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query needed
Once again i send the table data: Code Period Value c1 2004 22 c1 2005 10 c2 2005 15 c3 2005 20 c4 2005 15 c5 2005 5 c6 2005 30 c7 2005 25 c1 20065 c2 2006 15 c3 200640 c4 200630 From this I need the sum of values for period 2005 and codes c1+c2-c4-c5 (this is not constant its just an example there is lot of codes like this..) For ex:- the reulst that I want to get is: the value for code c1, period 2005 is 10 for code c2, period 2005 is 15 sum of c1 and c2 is 10 + 15 = 25 The value for code c4, period 2005 is 15 for code c5, period 2005 is 5 Sum of c4 and c5 is 15 + 5 = 20 Finally the result is (c1+c2) - (c4-c5) = 25 - 20 = 5 On 8/14/06, Chris [EMAIL PROTECTED] wrote: VenuGopal Papasani wrote: Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss sure. select c1 + c2 - c4 - c5; doesn't help you fix the problem because you haven't told us what the criteria is for the query.
RE: query needed
SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4', code, IF(code='c5', code, 0))) FROM datavalue; -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 11:26 AM To: mysql@lists.mysql.com Subject: query needed Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss regards, venu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
