| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
Comments:
holli: hi pplz i luv abbz!! channing tatum is such a hottie!! ily adrian luv ya 4 eva!! mwa!!!
<IdahoEv> depends on precisely what he means by "multiple cells contain the same value". If it's something like several people might have the same last name, that doesn't necessarily mean he should have a separate Table.lastNames.
<IdahoEv> :-)
<bobfield> people either think in too many dimensions or not enough
<IdahoEv> true
<tktktk> is there a mysql builtin (like AVG) for the variance of a column?
<flupps> tktktk: did you try VARIANCE() ?
<tktktk> nope
<IdahoEv> lol flupps beat me to it
<ShuaiKing> Hello
<flupps> tktktk: if you would have you'd saved yourself the embarrasment of asking a silly question! ;)
<tktktk> flupps, IdahoEv ; thanks
<bobfield> !man mathematical functions
<SQL> (Mathematical Functions) : http://dev.mysql.com/doc/mysql/en/Mathematical_functions.html
<myc> http://pastebin.com/577403 << whats wrogn with this query
<flupps> !man group by functions
<ShuaiKing> Hello!
<SQL> (GROUP BY (Aggregate) Functions) : http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html
<janhaa> IdahoEv, http://home.flodhest.net/div/mysql.txt?embed=true
<ShuaiKing> Stupid question
<ShuaiKing> How can I do a "SELECT" in a field "id" that is, say, "500"
<ShuaiKing> er, 500
<IdahoEv> Rule of thum: "google first". It's tho top hit if you search "mysql variance function"
<flupps> who is thum?!
<IdahoEv> ha
<myc> janhaa: that your sute?
<myc> site*
<janhaa> myc, yes
<IdahoEv> janhaa: why specifically 2 and 4?
<myc> janhaa: i liek it
<janhaa> myc, thanks
<snoyes> janhaa: I'm guessing you want the rows containing the group-wise maximum of a column, right?
<bobfield> ShuaiKing, can you elaborate?
<janhaa> IdahoEv, example: since row 3,4,5 has col1="b" but row 4 has the highest values in that "group" ...
<tktktk> in display; can I force all doubles to have the same format?
<tktktk> by display; I mean select
<janhaa> snoyes, guess something like that :/
<IdahoEv> if snoyes is right, you want "SELECT MAX(col2) FROM table GROUP BY col1"
<archivist> !tell Shuaiking about tutorial
<ShuaiKing> Hmm
<SQL> Alrighty.
<snoyes> not if you want the whole row
<janhaa> IdahoEv, i'll try that...
<ShuaiKing> lol thanks
<tktktk> !tell tktktk about tutorial
<SQL> Alrighty.
<bobfield> *slaps the bot
<snoyes> http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html
<snoyes> note that it's very different for version pre-4.1
<IdahoEv> someone correct me if I'm wrong: "SELECT *, MAX(col2) AS localmax FROM table GROUP BY col1 HAVING col2=localmax";
<snoyes> you're wrong.
<IdahoEv> damn. :-)
<IdahoEv> how close did I get?
<yock> run it and find out?
<snoyes> problem is that there is no guarantee which col2 value you'll get with the GROUP BY clause in place.
<IdahoEv> oh yeah, that makes sense
<snoyes> for a simple table like this, you could select col1, max(col2) from table group by col1;
<yock> IdahoEv, you probably want to select col1 DISTINCT as a subquery first
<IdahoEv> yeah; i figured it's simple enough that there had to be a way to do it without a subquery
<snoyes> see the above link for the sub query solution, and the 4.1 version of the manual for the non-subquery solution
<yock> indeed
<IdahoEv> oh, you know i didn't even notice that snoyes' URL was part of this answer
<tktktk> can I force the display of a select * do display doubles in the format of "%6.2f" ?
<shadowarts> is it safe to increase the lengh fo the user feild in the user table?
<bobfield> shadowarts, in the mysql database?
<flupps> tktktk: can use the ROUND() function to format
<shadowarts> bobfield, yes
<yock> tktktk, if the values aren't double in the first place, what do you expect to gain?
<janhaa> i'm more accurate this time: http://home.flodhest.net/div/mysql.txt?embed=true -- i hope :/
<snoyes> tktktk: there is also the FORMATstring function
<IdahoEv> tktktk: are you representing a monetary value?
<bobfield> shadowarts, I wouldn't, personally
<janhaa> IdahoEv, did you allready answer me? :P .. i'll try it :)
<IdahoEv> janhaa: don't try my answer, it was wrong. Use the url that snoyes pasted: http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html
<janhaa> ok
<archivist> shadowarts no, as the source needs patching to do that
<shadowarts> hmm
<janhaa> okey, so i had to use nested select? :)
<tktktk> I'm not trying to print monetary values;
<tktktk> I just want a c like fprintf control over how doubles appear :-)
<IdahoEv> tktktk: if you want to ensure that your value has two decimal places, you can just store it as DECIMAL(6,2) instead of DOUBLE.
<yock> FP data types are a convenience =)
<tktktk> select ROUND(VARIANCE(...)) ... <-- looks ugly as table column because really wide; can I have it display that, but have rnd_var as the column title?
<IdahoEv> select ROUND(VARIANCE(...)) AS rnd_var
<yock> !man aliases
<SQL> (Database, Table, Index, Column, and Alias Names) : http://dev.mysql.com/doc/mysql/en/Legal_names.html
<tktktk> awesome; one more stupid question; can I redirect the outpu of a select to a file?
<IdahoEv> floats have caused all of the most awful application bugs I have ever had to deal with in my own code
<bobfield> I have to agree, especially financial apps
<IdahoEv> tktktk: depends where you're running the select from.
<yock> decimal points are a display issue and should be handled by the application
<EricCartman-iBoo> Can I format the data in a DECIMAL 10,2 column, remove the part after the dot AND make the dot a thousand seperator
<tktktk> mysql
<tktktk> I'm in a mysql session
<snoyes> tktktk: view the help
<tktktk> oh
<snoyes> it mentions tee, which I think it \t or /t or something
<tktktk> into outputfile hehe
<tktktk> thanks
<IdahoEv> SELECT .... INTO OUTFILE
<IdahoEv> I've had code "if ($value != 6283) print $value" output the text "6283" in a financial application before. And the SQL types were even decimal(10,2) rather than float. took me about 5 hours to figure out what was happening.
<snoyes> So just remember to test decimal values for nearness rather than equality.
<IdahoEv> (php implicit conversion to float type in the code that computed $value four lines earlier; $value was actually 6283+1.07e-23)
<snoyes> (along with everything else you have to remember while coding)
<bobfield> I've run into some tricky precision issues with mysql
<archivist> reminds me of Autocad output where the lines dont join
<IdahoEv> i certainly remember it more quickly now. That was a nightmare day; the client was waiting.
<janhaa> seems like this works: select col1,col2,col3 from t1 where col2=(SELECT MAX(col2) from t1 GROUP BY col1) group by col1
<jessecrouch> ive got a table "answers" that stores answers for each user.. usually about 10 each. i need that data, but i also dont want to m*** query the db for EVERY single answer when i want to see all of them.. the answers are all numbers and add up to what i want to see anyway. should i just create another table that contains the full sum of the numbers? or is there a better way to do it?
<IdahoEv> hey, this reminds me; i have a nagging problem with one of my tables that has similar behavior but I don't think it's a precision issue.
<IdahoEv> lemme dig up the code
<bobfield> I have some GPS data that goes to 15 places
<IdahoEv> jessecrouch: if i understand your question, you want something like "SELECT SUM(column) WHERE condition", perhaps
<jessecrouch> right. thats what i want the end result to display, but i dont think i want to have to query the db that way every time since the answers arent going to change very often. im thinking it would be more efficient to store the sum for each user in some other table
<IdahoEv> jessecrouch: how often are you going to query them? DBMs are fast these days; you may be worrying about efficiency a lot more than you need to.
<snoyes> jessecrouch: that is a denormalization approach. I'd only consider that if I had several million rows in the table.
<IdahoEv> ^ what he said
<jessecrouch> hm. well. ill show you. hang on
<IdahoEv> it's only worth doing that if you have constructed good indexes for the table and your queries are still taking several milliseconds
<LabThug> What Collation do I want for my new databases?
<IdahoEv> LabThug: what language do you speak?
<LabThug> American English/ I mean gibberish ;-)
<IdahoEv> Do you want your searches/matches to be case sensitive, or non-case-sensitive?
<bobfield> we get to choose?
<jessecrouch> just about every user that logs in is going to see this graph http://dev.ifakedit.com:3000/images/graph01.png its going to have a bunch of points plotted on it.. points for each user on the system that show where they are on the graph. the two axes are represent the totals im considering keeping separate from the answers so i dont have to do SUM every time it gets queried
<IdahoEv> that's the difference between setting collation to latin1_general_ci vs. latin1_general_cs
<LabThug> IdahoEv, contents should be case sensitive, but not column names
<jessecrouch> so.. like.. imagine 100-1000 users each with 10 answers each that need to be SUM'd twice to make a dot on that graph
<LabThug> ahhh, gotcha
<IdahoEv> how often do you need to make different graphs
<snoyes> jessecrouch: in that case, I'd just generate the image every time the data changed, but not for each log in
<IdahoEv> yes, cache the image file
<\gro> !man join
<SQL> (JOIN Syntax) : http://dev.mysql.com/doc/mysql/en/JOIN.html
<IdahoEv> generating the image probably takes more time than querying the database
<snoyes> on second thought, that might not work so well if each image is user specific
<jessecrouch> the graph doesnt have to change all that often at all actually.. only when a new user is added
<jessecrouch> well the image is actually just a background image.. the dots on it are generated with js
<jessecrouch> because i didnt want serverside load =)
<IdahoEv> write it the easy way first to get it done. Then see if it needs optimization before spending time doing it.
<snoyes> How different is each user's view of the graph? Is there a special "you are here" marker?
<jessecrouch> yeah. theres one youarehere and then theres all the other dots showing the other users
<jessecrouch> the SUMs wont change very often
<jessecrouch> just.. users get added and another dot goes on
<snoyes> On the server side, generate the one image with everybody's dots. Do this whenever the table changes.
<jessecrouch> but i think you're right.. looks like ill just do it the easy way first
<bobfield> I generally prefer case insensitivity but it'd be nice to override it temporarily
<IdahoEv> Killing yourself over unnecessary optimization in your code or database is one of the most common development mistakes. 90% of the time you find out your performance bottleneck is somewhere else and you wasted your time.
<snoyes> Then on login, add the specific user's dot. Abstract that functionality in such a way that you can change the query easily.
<yock> snoyes, that could even be a stored procedure, yes?
Return to
#mysql
or
Go to some related
logs:
#css
wpa_gui livecd
#web
#web
#perl
ubuntu wmvdmod.dll
#gimp
sed dirname
#suse
Fire GL1 IBM xorg.conf
|
|
