#mysql
12 November 2007
Total 20 pages. You are browsing page 1/20.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Mon Nov 12 00:00:35 2007
00:17 <****> randoms: back
00:19 <****>
00:38 <****> sweet
00:45 <****> hello
00:45 <****> hi rmaj
00:46 <****> if I have table of people that belong to a city: users (id, name, city_id) and table of cities (id, name), then how to get a list of most populated cities?
00:46 <****> !tell us about group-wise
00:46 <****> domas asked me to tell you this: For info on getting the MAX of a GROUP BY see http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html and http://jan.kneschke.de/projects/mysql/groupwise-max/
00:47 <****> oh sweet domas we have that, cool
00:47 <****> thats the old one
00:47 <****> until archivist shows up
00:47 <****> heh, "until"
00:47 <****> heh
00:48 <****> rmaj: you'll just need to join to get a city name instead of a city ID
00:48 <****> but then you're done
00:51 <****> how do i get mysql with apt get?
00:51 <****> brandonc: you use lolbian?
00:51 <****> rmaj whats that?
00:51 <****> then simply apt-get install mysql or mysql5
00:52 <****> brandonc: lolbian is nickname for the system that provides 1.5 year old versions of some applications
00:52 <****> no i want most recent version of mysql
00:52 <****> that is five?
00:52 <****> debian have allways old software
00:53 <****> but debian testing have some 5.x mysql server and client (although not the newest one)
00:53 <****> SELECT count(*),group_id FROM group_members GROUP BY group_id;
00:53 <****> ^- this works
00:53 <****> brandonc: 6.0.2 is newest available \o/
00:53 <****> SELECT count(*),(SELECT name FROM group WHERE id=group_id) FROM group_members GROUP BY group_id; this fails, how to do it? or using JOIN?
00:53 <****> though you can always bk-build newer one :)
00:54 <****> SELECT count(*),g.name FROM group_members gm JOIN group g ON g.id=gm.group_id GROUP BY group_id;
00:54 <****> ^-- this also fails wtf
00:56 <****> rmaj: output the result without group by and check first
00:56 <****> kimseong: yeap that works fine
00:56 <****> rmaj: maybe with a select * and see what the result looks like before you group and count
00:57 <****> rmaj: and do a manual count to see if that is what you want
00:59 <****> kimseong: http://rafb.net/p/T7zpbh78.html
00:59 <****> what version?
01:00 <****> ops
01:00 <****> !man reserved word
01:00 <****> (Treatment of Reserved Words in MySQL) : http://dev.mysql.com/doc/mysql/en/reserved-words.html
01:11 <****> kimseong: hmm do you know how to fix it?
01:12 <****> ah it works
01:13 <****> SELECT count(*),g.`name` FROM `group_members` gm JOIN `groups` g ON g.`id`=gm.`group_id` GROUP BY `group_id`;
01:15 <****> rmaj: yep, sorry, had to run to safeway for a sec
01:21 * ed0n is away (auto-away after 2hrs of inactivity) - (01:25 am)
01:26 <****> hi guys, what is an aggregate table? Is it the same as a view? in the sense that it is a table composed of data from other tables?
01:27 <****> no
01:27 <****> if you're referring to a summary table
01:28 <****> wait, so an aggregate table is a summary table? and a summary would be a....?
01:30 <****> donato_home: where do you see anything about aggregate tables?
01:31 <****> I know of aggregate functions but I don't think mysql has anything called an "aggregate table"
01:51 <****> i'm interested in synching password data between a table and a linux account. Should encrypt() show me the same values as in /etc/shadow?
01:55 <****> how can i select 5 distinct `agent` from each `site`?
01:56 <****> and also the count of each of thouse `agent`
01:56 <****> scotepi: group by
01:56 <****> select agent, count(*) from site group by agent;
01:59 <****> let me re state, i have a table `log`, i want to select the top 5 `agent` and get the count of that `agent`, grouped by `site`
01:59 <****> hi
02:00 <****> what is the behavior of the '@' in a mysql query ?
02:00 <****> its a variable
02:01 <****> where is it stored ?
02:01 <****> memory
02:01 <****> how can I find out the available variables ?
02:01 <****> what are you looking at
02:02 <****> you can show variables;
02:03 <****> if I do a: select @wait_timeout, why does it return NULL ?
02:04 <****> its global use @@
02:06 <****> is it posable to do what i asked?
02:07 <****> is there a way to query the server and give me the date in unix time? The time in the db is Oct 31 2007 2:15PM which I'm unable to use in php without a lot of translation work
02:08 <****> !man date and time functions
02:08 <****> (Date and Time Functions) : http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
02:08 <****> there are functions there that convert between unix time and mysql date fields
02:09 <****> how is the NULL interpreted in a select query ?
02:09 <****> Well thank you ToeBee!
02:09 <****> codin: as null
02:10 <****> if you have something like: SELECT ... WHERE id='1' OR NULL AND user='test';
02:10 <****> it seems that it ignore the AND clause
02:10 <****> what am i doing wrong that i get this Cannot add or update a child row: a foreign key constraint fails
02:11 <****> codin: it's where (id='1' or id is null) and user='test'
02:11 <****> id=1 or null, doesn't work :)
02:11 <****> what should be null :)
02:12 <****> there is no user test in my table, yet it returns something
02:12 <****> anyone please?
02:12 <****> codin: well if it's returning smething, what is it returning :)
02:12 <****> jmoncayo: It means that the value you're inserting already exists
02:13 <****> the information for id==1,
02:13 <****> the foreign key is not the primary key and the table is empty anyways
02:13 <****> coding: that's a clear indication your query was faulty :)
02:13 <****> Goodspeed, yes I know that is clear, but why does it return that value ?
02:14 <****> jmoncayo: well, I suspect the table is using the innodb engine, what it could mean then, is that the id your inserting does not exists in the referenced table
02:14 <****> coding: because you're query is bad.. write a proper query
02:14 <****> :)
02:14 <****> I was trying to understand how does mysql parse the quesry
02:15 <****> Well first the id=1, then NULL, then user='test'
02:15 <****> it doesn't take id as null
02:16 <****> the where clause is the location where you check a field against a value (or lack thereof)
02:16 <****> so just typing "null" doesn't do much :)
02:17 <****> to me it seems that it ignores the AND clause, at least that is the effect
02:17 <****> Then that's it..
Total 20 pages. You are browsing page 1/20.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last