#mysql
03 November 2007
Total 17 pages. You are browsing page 1/17.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Sat Nov 03 00:00:10 2007
00:01 <****> federico__: you can get the min and max of t.id
00:01 <****> ki77a77: no, I want the max of count
00:02 <****> ki77a77: I guess I should compute this in the client program, and not in the rdbms
00:02 <****> federico__: the count is the count, 5, 9, 3012, whatever.
00:02 <****> ki77a77: select count(t.id) .... left join ... group by t.id
00:02 <****> max(count(
00:02 <****> the above counts relations, and outputs MULTIPLE rows
00:03 <****> it doesn't work max(count( :(
00:03 <****> of course it does
00:03 <****> which versoin?
00:03 <****> Trengo: SELECT tag.id, tag.name, count(tag.id) as cnt, max(count(tag.id)) as max FROM `article_tag` left join tag on tag_id=tag.id group by tag.id
00:04 <****> #1111 - Invalid use of group function
00:04 <****> 5.1
00:04 <****> no, mysql-5.0.44
00:04 <****> to begin with, you're selecting cols you dont group by
00:05 <****> I want to know the count() which is relative to
00:05 <****> try SELECT tag.id, max(count(tag.id)) as max FROM `article_tag` left join tag on tag_id=tag.id group by tag.id
00:05 <****> probably I can't do all at once then
00:06 <****> same error, Trengo
00:07 <****> I think it's a subquery job, but I want to stay 4.0 compatible
00:07 <****> sinche I have to iterate results to fetch it, I can compute min/max in the client script
00:07 <****> just wanted to know if I was doing some mistake
00:08 <****> i think i've done something like that recently
00:08 <****> of course perhaps it was on pgsql :s
00:10 <****> oh
00:10 <****> it's a simple problem
00:10 <****>
00:11 <****> I want to count most used tag
00:11 <****> or more generally reference count of tags
00:11 <****> then get the min/max of this count in order to make a proper scale
00:12 <****> You want to find out which article references a tag the most?
00:12 <****> i.e.: I want "importance" range from 1 to 10 in 10 steps, so I would quantize count() and scaling it according to min()/max() value
00:13 <****> Thecks: I am doing that "tag cloud" seen in some blogs
00:13 <****> where the most a tag is used, the bigger it is displayed
00:13 <****> So you're just looking for the most used tag?
00:13 <****> Well, order them by the most used?
00:13 <****> Thecks: not properly
00:14 <****> Eh?
00:14 <****> I need min(count()) and max(count()) to scale that count() value in a proper range of font sizes
00:15 <****> an example of tag cloud: http://tinysubversions.com/tag_cloud.png
00:15 <****> Yeah I know of a tag cloud
00:27 <****> federico__: You could try this..
00:27 <****> SELECT DISTINCT tagname, count(*) FROM tags GROUP BY tagname ORDER BY count(*) DESC;
00:28 <****> In fact instead of using count twice
00:28 <****> SELECT DISTINCT tagname, count(*) as counted FROM tags GROUP BY tagname ORDER BY counted DESC;
00:28 <****> yes, I am ordering by count
00:28 <****> but actually computing min/max in the client code
00:28 <****> Making it give numbers 1 to 10 based on how many there are... perhaps that's something left to the PHP or similar?
00:29 <****> You're going to have to loop through the results anyway... why not just increase the font size each result?
00:29 <****> yes, normally I loop once through the results.
00:30 <****> this time I loop a second time, to re-scale count() value according to min()/max()
00:30 <****> * min()/max() are values computed in the client
00:30 <****> You're not making much sense to me
00:31 <****> why? I made a function getTagCloud(q) where q is the quantization level
00:31 <****> e.g.: with q=3 I just get count ranging to 0...3
00:31 <****> this way I always use all font size, and respect a proportion with the real count value
00:32 <****> The majority of that should be handled by your script
00:32 <****> So q is actually the maximum you want?
00:32 <****> Well.. the number of results returned?
00:33 <****> yes q is the maximum
00:33 <****> SELECT DISTINCT tagname, count(*) as counted FROM tags GROUP BY tagname ORDER BY counted DESC LIMIT 0, ' . q .';'
00:33 <****> but imagine one thing: every tag is used at least N times
00:33 <****> Right...
00:33 <****> if N bigger enough, I don't use the smallest font size perhaps
00:34 <****> so here's how I re-scale count in the script
00:34 <****> So you want to use ALL the results? or you only want to use Q amount of results?
00:34 <****> $r[$id]['count'] = ($r[$id]['count'] - $t_min) * $q / $delta;
00:34 <****> $delta = $t_max - $t_min;
00:35 <****> What is the answer to my question?
00:35 <****> Thecks: yes, I want all results, and their count() range is rescaled to 0....$q
00:35 <****> I'll use pesudo code for you
00:35 <****> SELECT DISTINCT tagname, count(*) as counted FROM tags GROUP BY tagname ORDER BY counted DESC;
00:36 <****> LOOP THROUGH RESULTS
00:36 <****> FONTSIZE + 1
00:36 <****> DISPLAY RESULT
00:36 <****> END LOOP
00:36 <****> And order it from the least used to the highest
00:36 <****> That will make the font get bigger as the tag is used more
00:37 <****> ok, that works too. replacing 1 with $q/$total_num_of_records gives me also control on max font size
00:37 <****> MySQLs job is to retreive data and sort it into order for you, not to quantanize it or apply a mathmatical formula to it
00:38 <****> Exactly :)
00:38 <****> well, I'm used to do also some math in MySQL :)
00:38 <****> Yes, but if you look at MySQLs mathmatical library it's not that complex
00:39 <****> It's usefull for getting things like basic averages and MAX, MINs
00:39 <****>id}
00:39 <****> above is an example of average rating tih no external tables
00:39 <****> rating and nrates are table fileds
00:40 <****> nrates being number of rates?
00:40 <****> yep
00:41 <****> at every rating action, it computes a new average, giving a weight of nrates to the old rating, and a weight of 1 to the new rating
00:41 <****> That still counts as basic mathmatics :)
00:41 <****> yes, of course ;p
00:41 <****> And it would be silly to get the current results, add to them and then update them in another query
00:41 <****> It would probably lead to problems doing it that way as well :D
01:10 <****> I'm looking to create some kind of constants that are used in queries, routines, and probably other places I haven't thought of yet
01:10 <****> One way I can think to do it is with functions that just return the value, but maybe there's a better way?
01:11 <****> a user defined variable?
01:11 <****> You could create a table with references in?
01:11 <****> when I do an insert of more than 50 characters into a VARCHAR(50) column, is there an auto-truncate or an error?
Total 17 pages. You are browsing page 1/17.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last