matchoo

Ok, I think this would work.... Once again, benign neglect does the trick

Sometimes, I just can't get my head around the SQL I need to create. Here's the table structure (blogstats) CREATE TABLE `blogstats`.`daily_totalblogs` ( `blogSource` char(2) NOT NULL default '', `totblogs` bigint(21) NOT NULL default '0', `day` date default NULL, UNIQUE KEY `uq_idx` (`blogSource`,`totblogs`,`day`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 It's basically a table which contains counts for blog articles on different blog sources (techcrunch, slashdot, etc) by the day that the count was tallied. First query I wrote was very straightforward: select blogSource, max(day), sum(totblogs), totblogs as totblogs_inserted_last_time from daily_totalblogs group by blogSource order by totblogs_last_count desc Ok, so this gets me a list of the sum of the blogs articles counted, per blog, and the number of blog articles inserted on the day that the latest blog count was made / inserted. BlogSourceMax(day)sum(totblogs)totblogs_inserted_last_time SlashDotApril 2812,5438 TechCrunchApril 261,1466 GizmodoApril 271,03310 What I need to get is the number of blogs that were inserted the time BEFORE the last count was made. BlogSourceMax(day)sum(totblogs)blogs_inserted_last_timecount_before_last_datetotblogs_count_before_last SlashDotApril 2812,5438April 2511 TechCrunchApril 261,1466April 229 GizmodoApril 271,03310April 108 Point me in the right direction? I understand the MYSQL date functions, to calculate the previous day, but that 'time_before_last' is different for each blog network. So calculating the date is not useful in this case. Is a subquery the answer? HOw would that look?

Hey. Never claimed to be a SQL expert, so here we are... first the query, then the problem. It's a database about video clips. Query looks like this... SELECT engagementIndex,uniques FROM ts_clip_beacon_day LEFT JOIN ts_clip_view_day ON ts_clip_beacon_day.clipXid = ts_clip_view_day.clipXid WHERE ts_clip_view_day.clipXid <> '+' AND ts_clip_view_day.year = 2007 AND ts_clip_view_day.dayofyear = 304 ORDER BY engagementIndex DESC LIMIT 200 So, this works, but it's not really what I want. This gives me the top 200 by engagement. What I want is the top 200 by UNIQUES which is in ts_clip_view_day but sorted (ORDERED) by engagementIndex which is in ts_clip_beacon_day I'm sure it's simple... but I'm having brain farts. Anyone... anyone... bueller?

Hey there, I am using MySQL 5.1 I have an HTML grid of 16 different images. Each image has an img_type. I want to return exactly 16, with img_type prioritized as follows: If there are 16 img_type = 'people' I want to return them all If there are less than 16 img_type = 'people' I want to return as many as possible, and populate the rest with img_type = 'other' Again, I need exactly 16 images, but comprised of different types based on how many 'people' are in the database. Is this possible using CASE statements? If so, I would love a little help and a generic example. Thanks. -Matt

Saw this shirt, thought it was pretty funny. http://www.cafepress.com/passwd

ok, I think it will take super long messages now.

Ok, I added the refresh captcha. I'll change the font later tonight. As for sending illegal info. Perhaps, but if the government wants me to decrypt, they would have to issue warrants and such, and I could likely take down the site altogether. But I am hopeful that criminals are not the ones interested in this stuff. They would be better off talking on Cell phones as usual.

Cool, thanks. I'll put a refresh on the Captcha, and test the database storage on long messages.

Ok, I changed the layout. I gotta say I miss the rounded corners though. Nifty corners coming soon.

Micah... Well, it's a plug, but it's also sincere. I want you to break it. I'll ask her what she used as a salt. I figured it was nothing too complicated as she's not very computer savvy. Agentsteal... Hey, nice find! Gonna keep reporting errors, though, until I get them all. Nobody's using this stuff for now. Soon I'll shut off error reporting, and kill that silly test page. Do you have a program that runs through commonly used pages or somethin? Or are you clairvoyant?

Hey there, http://lockbin.com Whenever I have sensitive information I need to send to someone via email, I Google 'encrypt mail message' looking for solutions, but everything would be too complex for my potential recipients. So I built something to take care of the problem. Now, I can send people credit card numbers, or login credentials to my server, or whatever, and I don't have to worry about it being sniffed by some jerk in an Internet Cafe. It seems to work well for me, and for other folks, but one user in particular keeps telling me that her messages come back empty. Can't reproduce. Anyhow... Go ahead and test it. One nice thing about it is that you can read the JavaScript in your browser and see that nothing is ever sent to the server without being encrypted by your own "Secret Word" - thus, I can't read your messages even if I wanted to. Plus, the messages get deleted as soon as they are picked up. It is also using HTTPS. Nothing is fool proof, but this seems to be a good layman's alternative to S/MIME. -Matchoo

Ok, now I want to get the difference between a value in the last record and the current record... any way of doing that in SQL?

Can't do that because it's for multiple days. To clarify. I want the last entered hit count per day, for each day. Not for one day... a list of days.

Hi there, I have a log file with multiple records per day. I want to get the last log entry per page for a certain day only. select distinct date_stamp, page, hits from log won't work because there are different hit counts - so the query returns duplicate entries for a page how to get the last timestamp per day? (p.s. - I'm on 4.1 not 5)

Hi there. I need some SQL advice. I have a database of website usage data. There is a table named 'pages' and it has the following columns: page_id date_logged comment_count view_count I have been asked to figure out if comments are a predictor of page popularity (view counts). I would like to do a query that returns week to week variance numbers for comments and views and chart it. Then sort by highest current view counts. Does that sound like a good way of getting this information? Anyone feel like throwing a SQL query up to show an example of what that might look like? Thanks, Matchoo

Here's a new site I just put up, along with tutorial on how it was built. Basically pulls youtube data and charts it. http://youtubedata.com