|
Lancer383 posted:I think this will work, but it's just off the top of my head: Won't that put all the hours into the month it went down in? I'm trying to get the hours that are in the next month count towards the next month.
|
# ? Mar 17, 2008 14:55 |
|
|
# ? May 13, 2024 23:56 |
|
Charun posted:I have a date related problem. I would create a table of months with their beginning date (00:00:00) and ending date (23:59:59) and join against that. You'd want to get the month the event started in, the event the month ended in, then all the months between there. The most straightforward solution in Oracle syntax follows. MySQL 5.0 syntax should be very close to this. code:
code:
|
# ? Mar 17, 2008 16:22 |
|
I'm getting kind of busy, so if you have a problem that hasn't been addressed for several days in this thread, feel free to PM me with a link to the original post, as well as follow-up information from subsequent posts, and create table statements/test data, desired output. Make it easy for me to help you, and I'll do my best.
|
# ? Mar 17, 2008 17:43 |
|
var1ety posted:I would create a table of months with their beginning date (00:00:00) and ending date (23:59:59) and join against that. You'd want to get the month the event started in, the event the month ended in, then all the months between there. Thank you so very very much. Here's what I ended up with: code:
|
# ? Mar 18, 2008 04:10 |
|
Charun posted:and it works perfectly! Do you want plat or archives? No thanks, I just contribute for fun.
|
# ? Mar 18, 2008 16:38 |
|
This is relating to a book database, I have no idea how to word this query. For each genre, list the genre, all of the publishers of that genre, and owner of each publisher. The problem: each of these are in different tables and I don't know how to format this without it looking like total poo poo. Ideally, it'd be something like code:
|
# ? Mar 19, 2008 01:11 |
|
Isn't it as simple as:code:
|
# ? Mar 19, 2008 02:42 |
|
Whilst farting I posted:This is relating to a book database, I have no idea how to word this query. Minato's example will get you there, except it will look like: code:
|
# ? Mar 19, 2008 04:24 |
|
Sorry, misposted!
Puddleboy fucked around with this message at 19:10 on Mar 19, 2008 |
# ? Mar 19, 2008 19:06 |
|
Lt Moose fucked around with this message at 05:38 on Apr 4, 2016 |
# ? Mar 20, 2008 00:42 |
|
minato posted:Isn't it as simple as: You're right, it is. The "order by" part escaped me. Thank you! Lancer383 posted:Minato's example will get you there, except it will look like: Yeah, it did seem like a bit of an odd request. Is there any way to display comments when running sql in batch mode? // is executed literally, # is ignored, as is /*...*/. The --comments command was introduced in MySQL 5.1.23 and the network is running 5.0.24. Whilst farting I fucked around with this message at 00:56 on Mar 20, 2008 |
# ? Mar 20, 2008 00:49 |
|
I'm trying to select in MySQL one row for each auto in my Photos table where the orderid column is the lowest value:code:
Can anyone give me a hint on how I can get around this? edit: Nevermind it seems I found a way around it by: code:
Stephen fucked around with this message at 16:35 on Mar 20, 2008 |
# ? Mar 20, 2008 16:28 |
|
Stephen posted:I'm trying to select in MySQL one row for each auto in my Photos table where the orderid column is the lowest value: code:
|
# ? Mar 20, 2008 16:43 |
|
Yeah, I figured I could do it with a join, but I was hoping there was a simple obvious answer that I was too stupid to see.
|
# ? Mar 20, 2008 16:58 |
|
Stephen posted:Yeah, I figured I could do it with a join, but I was hoping there was a simple obvious answer that I was too stupid to see.
|
# ? Mar 20, 2008 17:53 |
In MySQL let's say you have two tables, users and articles, and articles have a column keeping track of the number of hits. I want to get a list of users and the total number of hits that articles they own have gotten, ordered by number of hits descending. Something like: SELECT user.username, SUM(article.hits) AS totalHits FROM user JOIN article ON article.owner = user.id GROUP BY article.id ORDER BY totalHits DESC LIMIT 20 What column am I supposed to be grouping by? article.id seems to work alright, but I don't really understand why. This query is also very slow, is there any way to speed it up aside from keeping a totalHits count in my user table?
|
|
# ? Mar 20, 2008 21:22 |
|
fletcher posted:In MySQL let's say you have two tables, users and articles, and articles have a column keeping track of the number of hits. I want to get a list of users and the total number of hits that articles they own have gotten, ordered by number of hits descending. So for each user, there are multiple articles? You want to know how many hits all of their articles together have gotten? It looks like your query will only work if each user only has one article. I think you want to group by the article owner, summing the total hits across multiple articles for each owner: code:
|
# ? Mar 20, 2008 21:29 |
|
If I want to store a users prefrence, and it is just going to be an on or off value, should I use a tiny int field type? Or is there a better way?
|
# ? Mar 20, 2008 22:07 |
|
I'm pretty sure there's a boolean type for column values. However, I use a tinyint(1) for flags like that, just in case a third option creeps up at a later date.
|
# ? Mar 20, 2008 22:12 |
Puddleboy posted:So for each user, there are multiple articles? Ah, that is what I needed. So what about the performance issue? It takes ~4s to run this query.
|
|
# ? Mar 21, 2008 02:38 |
|
fletcher posted:Ah, that is what I needed. So what about the performance issue? It takes ~4s to run this query.
|
# ? Mar 21, 2008 06:46 |
minato posted:Make sure you have an index on both article.owner and user.id. user.id is the primary key for the user table For the article table I have article.id as the primary. There are also separate indexes for article.owner, article.pictureId, and article.hits. When I do an EXPLAIN for the slow query, I see this: possible_keys: owner key: null ref: null rows: 315615 (which is the size of that table) Extra: Using where; Using temporary; Using filesort edit: Side question, but how do I chose my CHARSET and COLLATE for a table? fletcher fucked around with this message at 07:47 on Mar 21, 2008 |
|
# ? Mar 21, 2008 07:18 |
|
How can I sort a table like this:code:
code:
nbv4 fucked around with this message at 06:37 on Mar 24, 2008 |
# ? Mar 22, 2008 13:05 |
|
nbv4 posted:See how I did that? The sorting is based on two different columns at the same time. An easy solution would be to make a third column that is just the two columns added together, but I suck at SQL so hard I can't even figure out how to do that If you're using MSSQL, this will work (just tested it). code:
Boogeyman fucked around with this message at 19:12 on Mar 24, 2008 |
# ? Mar 24, 2008 18:56 |
|
Boogeyman posted:If you're using MSSQL, this will work (just tested it). That actually doesn't work because value1 is actually "SUM(another_value) AS value1" so it gives me this error: "#1247 - Reference 'value1' not supported (reference to group function)" I think making a third column would be best because some instances there may be a value in both value1 and value2.
|
# ? Mar 24, 2008 23:03 |
Anybody have any ideas about my indexing/performance issue a few posts up? Or about CHARSET and COLLATE?
|
|
# ? Mar 25, 2008 19:09 |
|
nbv4 posted:That actually doesn't work because value1 is actually "SUM(another_value) AS value1" so it gives me this error: "#1247 - Reference 'value1' not supported (reference to group function)" You can't sort on grouped fileds, but you can do it (sort) in an outer select: code:
Alex007 fucked around with this message at 19:48 on Mar 25, 2008 |
# ? Mar 25, 2008 19:37 |
|
I have a generic question about indexes in SQL Server 2005. Let's say I have the following tables (PK = primary key, FK = foreign key)... Customer -CustomerId (PK) -CustomerName Account -AccountId (PK) -CustomerId (FK) -AccountNumber Device -DeviceId (PK) -AccountId (FK) -DeviceName -PhoneNumber MSSQL automatically creates indexes on primary keys, so after creating these tables, I already have an index on Customer.CustomerId, Account.AccountId, and Device.DeviceId. Now, common sense says that I should have indexes on the foreign keys to boost MSSQL join performance. So, I add indexes on Account.CustomerId and Device.AccountId. Finally, I want to search on a couple of fields in my application, so I add indexes for Customer.CustomerName, Account.AccountNumber, and Device.PhoneNumber. So, for indexes, I have individual indexes for the following tables/columns... Customer.CustomerId Account.AccountId Device.DeviceId Account.CustomerId Device.AccountId Customer.CustomerName Account.AccountNumber Device.PhoneNumber Here's the question. Am I OK leaving all of these indexes as single column indexes, or would I be better off combining some of them? For instance, instead of having separate indexes for Account.AccountId and Account.CustomerId, or should I include both columns in the primary key index that was created with the table? Likewise for Device.DeviceId and Device.AccountId. I'm just wondering if MSSQL is smart enough to use the single column indexes efficiently, or if grouping the columns together would help it out. I know that there's no 100% correct answer and that I need to test all of this on my data set, but I'm just trying to find out if there's a general guideline for stuff like this.
|
# ? Mar 25, 2008 23:29 |
|
DISTINCT keyword Gents, I've been looking for a walkthru on the MySQL DISTINCT keyword and haven't found anything useful. I have a table that looks like this: user, vote, time_entered, ipaddress, blah1, blah2, blah3 User is a VARCHAR, vote is an INT. A user may vote many times, but I only want to take one vote from no votes in the average may share an IP address, no votes in the average may share a username I am using PHP to interact with MySQL Thanks!! functional fucked around with this message at 00:28 on Mar 26, 2008 |
# ? Mar 26, 2008 00:18 |
|
Alex007 posted:You can't sort on grouped fileds, but you can do it (sort) in an outer select: hmm, thats not working either. Here is my exact query: code:
code:
nbv4 fucked around with this message at 00:40 on Mar 26, 2008 |
# ? Mar 26, 2008 00:26 |
|
functional posted:DISTINCT keyword It sounds like what you want can be achieved with a group by statement. code:
noonches fucked around with this message at 00:39 on Mar 26, 2008 |
# ? Mar 26, 2008 00:31 |
|
nbv4 posted:as you can see, it seems to think by "ORDER BY" I really meant "RANDOMIZE BY" If I order it by either total or simulator alone, it works as expected, but adding that plus sign screwes it all up. In the outer SELECT, convert NULLs to zeros, because NULL + anything = NULL. code:
|
# ? Mar 26, 2008 00:54 |
|
noonches posted:
OK, what if I want each person's most recent vote? I don't think I can just add ORDER BY timed_added DESC LIMIT 1 onto the end of that query you've given me. That would just sort the already DISTINCT items...?
|
# ? Mar 26, 2008 00:58 |
|
functional posted:OK, what if I want each person's most recent vote? That wouldn't work, and I think what you need would make the query a bit more complicated, maybe not tho, I could be forgetting something simple. I can't put it together in my head at the moment. Here's a question, why would people be allowed to have more than one vote? Especially if you're not going to use them all like in this example.
|
# ? Mar 26, 2008 01:07 |
|
nbv4 posted:hmm, thats not working either. Here is my exact query: Just compute your sorting column in the inner query and sort on it in the outer select directly.
|
# ? Mar 26, 2008 02:59 |
|
noonches posted:Here's a question, why would people be allowed to have more than one vote? Especially if you're not going to use them all like in this example. The question you ask is complicated. The answer is that, assuming we can get the query to work, this is a simpler and better solution with fewer moving parts. We get the benefit of better logs, we give users the ability to change their votes, we don't have to code in (or maintain) restrictions on multiple voting, we don't have to assume our database is constantly in a secure state (we don't have to trust our restrictions), and we run fewer queries. You can also make a comparison to sanitizing input for XSS before it hits the database (bad) and right before it hits the output page (good). functional fucked around with this message at 03:10 on Mar 26, 2008 |
# ? Mar 26, 2008 03:02 |
|
I'm hesitant to post this, because I am almost positive there's a better way, and this is gonna get me scolded in here... but assuming you have an auto-incrementing field for the votes table, this should work. code:
|
# ? Mar 26, 2008 03:20 |
|
noonches posted:
I've been playing with this... When we GROUP BY on the two fields, we don't actually get what we're trying to do. It goes in the other direction: now the same user can vote twice if he comes from a different IP. And the same IP can vote twice if he comes from a different username (definitely not what we want). Why doesn't GROUP BY have aggregate functions like LAST() or FIRST()? This is ridiculous. This blog really hits the nail on the head: http://www.mysqlperformanceblog.com/2007/08/17/should-mysql-extend-group-by-syntax/ functional fucked around with this message at 17:44 on Mar 26, 2008 |
# ? Mar 26, 2008 17:39 |
|
functional posted:I've been playing with this... When we GROUP BY on the two fields, we don't actually get what we're trying to do. It goes in the other direction: now the same user can vote twice if he comes from a different IP. And the same IP can vote twice if he comes from a different username (definitely not what we want). Er, maybe I'm misunderstanding, but this doesn't make a lot of sense. If you have code:
It sounds like you're trying to apply a constraint to your data model after the data already exists, the constraint being that users:ip's have a 1:1 ratio for votes. Is that correct? Also, that blog is terrible because he advocates further extending MySQL's already dangerously broken group by syntax to be even more broken.
|
# ? Mar 26, 2008 18:08 |
|
|
# ? May 13, 2024 23:56 |
|
yaoi prophet posted:Er, maybe I'm misunderstanding, but this doesn't make a lot of sense. If you have Yes. It should be ignored. yaoi prophet posted:It sounds like you're trying to apply a constraint to your data model after the data already exists, the constraint being that users:ip's have a 1:1 ratio for votes. Is that correct? In the set of counted votes, no two usernames should match up, and no two IPs should match up.
|
# ? Mar 26, 2008 18:44 |