|
I gots me a query question: We're using SQL Server (2008 I think?) and my data is in a table with the following columns (simplified): PersonGuid Timestamp Item. These rows represent sales, so the table has millions of these rows, with thousands of rows per PersonGuid. What I was to do is somehow construct a query that will give me one result set of the most recent 5 Items per PersonGuid. I have no idea how to do this other than getting a list of every PersonGuid and for each of them get the most recent 5 Items, but I figure there might be an easier way to do this. Any ideas?
|
# ? Jul 16, 2010 19:22 |
|
|
# ? May 14, 2024 20:31 |
|
Ugg boots posted:I gots me a query question: untested code:
|
# ? Jul 17, 2010 00:40 |
|
latest dumb access question : I have a table of records of stores. each row has a UID storenumber. every week I update this information in my database with a datafeed I import. Some existing stores have data changed, and some new stores are added. Right now, I have two select queries, one that gives me a list of relevant stores that changed, and one that gives me a list of new stores. I've cleverly used a UNION to join these two queries into one, that just gives me all stuff I need to update/append into my master table. but of course when I use an update query to try and accomplish this, access throws up the error "Operation must use an updateable query." I don't know what the gently caress, but I'm assuming it's because I'm trying to update some records that don't exist (ie, I need to actually append the new records). I could just make seperate action queries to first update existing records, then append the new records, but why should I have to click all that stuff when there should be a way to just do them both at the same time. I don't know a lick of VB, and I am not seeing how I'd accomplish this with a macro. Is it possible to both append and update with a single action query? google is pathetic and worthless.
|
# ? Jul 19, 2010 20:58 |
|
Can you do a REPLACE INTO query in Access? If the data you are getting has complete records, this would work.
|
# ? Jul 19, 2010 21:02 |
|
sadly there's more data/columns in the store table than in the data I import, so if REPLACE INTO actually deletes records before inserting them, this wouldn't be an option...
|
# ? Jul 19, 2010 21:23 |
|
mindphlux posted:latest dumb access question :
|
# ? Jul 19, 2010 22:00 |
|
camels posted:untested Awesome! Works perfectly (and fast)!
|
# ? Jul 19, 2010 22:20 |
|
Jethro posted:It sounds like you're updating the query, not the master table. As such, you need to use "an updateable query", and queries which have joins or unions are explicitly not updateable. code:
can just like no part of an update query reference a join or a union query, or am I just being completely stupid and not getting something?
|
# ? Jul 19, 2010 22:50 |
|
mindphlux posted:
Try changing that to an inner join? You're UPDATEing the StoreData table, so it makes no sense to do a right join. It may be possible that no part of an update query can reference a union query, but given the way you described the union query that doesn't seem like a bad thing. Are you UNIONing the UPDATEs with the INSERTs? If so, stop it, you need two queryies anyway. One to UPDATE and one to INSERT.
|
# ? Jul 20, 2010 21:53 |
|
Hammerite posted:I think this is happening because your subquery is returning an empty set, which gets treated as NULL because it's being used in a context where MySQL expects a scalar. Now COUNT() usually returns an integer value, even if it's zero, but if you combine it with GROUP BY then it may not (if there aren't any GROUP BY values then the resultset is empty). So I think your WHERE clause is filtering out all the rows. The most likely reason for this is that VALUES is returning NULL. In the documentation, it states that "The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise". You're using it within the UPDATE clause, but inside a subquery, so I can only guess that VALUES wasn't designed to handle this situation, and it's returning NULL. Optionally, throw a case statement in there (which is often a good idea anyway if you have the possibility of returning a NULL in something like this) INSERT INTO bpamp (brand,publication,`date`,amp) ( SELECT brand,publication,`date`, case when count(bpId) is null then 0 else COUNT(bpId)/100 end FROM bp GROUP BY brand, publication, `date` ) ON DUPLICATE KEY UPDATE amp = ( SELECT case when count(bpId) is null then 0 else COUNT(bpId)/100 end FROM bp WHERE brand=VALUES(brand) AND publication=VALUES(publication) AND `date`=VALUES(`date`) GROUP BY brand, publication, `date`);
|
# ? Jul 21, 2010 03:33 |
|
stuph posted:Optionally, throw a case statement in there (which is often a good idea anyway if you have the possibility of returning a NULL in something like this) A question to the OP - Why is derived data (a simple record count) stored in a table? This looks dubious- I would look into using a view or query instead, making sure "bp" is indexed on (brand, publication, `date`).
|
# ? Jul 21, 2010 05:38 |
|
camels posted:A question to the OP - Why is derived data (a simple record count) stored in a table? This looks dubious- I would look into using a view or query instead, making sure "bp" is indexed on (brand, publication, `date`). The calculations are a lot more complex that I indicated in the OP, and the data is 1m+ records and expanding rapidly - I was just working with a simple example to get the basics figured out.
|
# ? Jul 21, 2010 11:23 |
|
So I suck rear end at SQL. Why am I getting "Unknown column 'distance' in 'where clause'" when executing this:code:
|
# ? Jul 21, 2010 20:58 |
|
Ferg posted:So I suck rear end at SQL. Why am I getting "Unknown column 'distance' in 'where clause'" when executing this: code:
|
# ? Jul 21, 2010 21:05 |
|
Jethro posted:There is no column called distance in your source table. distance is an alias for an expression in your select list. That surprises me, I thought you were able to use aliases in a WHERE clause? edit: never mind, quote:Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.
|
# ? Jul 21, 2010 21:16 |
|
Jethro posted:There is no column called distance in your source table. distance is an alias for an expression in your select list. Golbez posted:That surprises me, I thought you were able to use aliases in a WHERE clause? Thank you sirs!
|
# ? Jul 21, 2010 21:18 |
|
HAVING should also work.code:
|
# ? Jul 21, 2010 21:27 |
|
I need help.code:
Can I get MySQL to tell me which day had the highest number, without doing a second query of "select date_etc from logs where count(etc) = $thenumberIjustgot"? Or doing a 'order by count desc limit 1'? I'd like to think there are more elegant methods. I don't care about tiebreakers, I'll cross that bridge when I get to it. Golbez fucked around with this message at 23:01 on Jul 21, 2010 |
# ? Jul 21, 2010 22:54 |
|
I think you just need an ORDER BY in your sub query.code:
|
# ? Jul 22, 2010 00:21 |
|
Doctor rear end in a top hat posted:I think you just need an ORDER BY in your sub query. I'm not at work, but won't that order by the date, rather than the count?I mean, if I was going to resort to an order by, I'd just do it by the count and dispense with the MAX, but I was wondering if it was possible for MAX to give me the correct date.
|
# ? Jul 22, 2010 00:37 |
|
When you do an ORDER BY with a GROUP BY, the GROUP BY will keep the first record that the ORDER BY returns, plus calculate fields like counts. It should return the same thing as what you were getting, but with the newest dates. The whole query would look like this:code:
|
# ? Jul 22, 2010 00:47 |
|
As someone with very little experience with SQL, part of my job is to code SQL queries. My employers decided this was a good idea. Anyway, when I run this MS SQL query I get "Incorrect syntax near the keyword 'GROUP'." code:
code:
What I need is to be able to count how many unique picks there are per part number. For example, if I get the results from the subquery: PartNum___PickRef___Qty 12345_____ABCDEF____1 12345_____QWERTY____3 12345_____POIUYT____2 98765_____ABCDEF____1 98765_____LKJHGF____17 I want the final query to tell me that part 12345 has 3 unique picks for it, and 98765 has 2. I'm not entirely sure why I can't do "SELECT whatever FROM (SELECT whatever....)" in this case, since I've got other subquery thingies like that which seem to work fine. Thanks for any help!
|
# ? Jul 22, 2010 10:01 |
|
Doctor rear end in a top hat posted:When you do an ORDER BY with a GROUP BY, the GROUP BY will keep the first record that the ORDER BY returns, plus calculate fields like counts. It should return the same thing as what you were getting, but with the newest dates. The whole query would look like this: I tried it, and it gave me the strange result of 2010, 7, 22, and ... 4? I'm running this at 9am, not 4am, so I don't know why it gave me 4... our peak hour certainly wasn't the middle of the night. I think at this point I'll just dispense with the max and go with a simple order by. I'm still surprised there's no way to easily get the row from the max().
|
# ? Jul 22, 2010 15:27 |
|
Golbez posted:I tried it, and it gave me the strange result of 2010, 7, 22, and ... 4? I'm running this at 9am, not 4am, so I don't know why it gave me 4... our peak hour certainly wasn't the middle of the night. You're getting a count, a year, a month and a day. Where does the hour come in?
|
# ? Jul 22, 2010 15:30 |
|
Doctor rear end in a top hat posted:You're getting a count, a year, a month and a day. Where does the hour come in? Er, sorry, I guess I omitted that earlier, we're running hour, day, month, etc. I guess the example I pasted was day, but the one I ran was hour. But that doesn't really solve the problem of the fact that the actual max wasn't today, and definitely not at 4am.
|
# ? Jul 22, 2010 15:39 |
|
What do the records look like when you just run the inner query?
|
# ? Jul 22, 2010 15:43 |
|
Doctor rear end in a top hat posted:What do the records look like when you just run the inner query? A few examples of the day query: code:
|
# ? Jul 22, 2010 15:56 |
|
I was wondering if there was any gui tool like sqlyog or the mysql one that issues a warning on update statements missing a where clause. phpMyAdmin does this for delete statements, but I think updates are just as dangerous.
|
# ? Jul 22, 2010 19:18 |
|
Harry Krishna posted:I was wondering if there was any gui tool like sqlyog or the mysql one that issues a warning on update statements missing a where clause. You could insert an update trigger that rejects them on a per-table basis. The logic for that would be a bit complex when you look at where clauses that could still include the entire data set, but you could get pretty close.
|
# ? Jul 23, 2010 06:29 |
|
Question Mark Mound posted:As someone with very little experience with SQL, part of my job is to code SQL queries. My employers decided this was a good idea. code:
|
# ? Jul 23, 2010 18:52 |
|
Jethro posted:T-SQL chokes if you don't give aliases to inline views.
|
# ? Jul 26, 2010 08:59 |
|
I have a question about how mysqldump works. Does it freeze the database at the moment that it starts and dumps that frozen version or will it dump changes to tables as it gets to subsequent table names. I'm trying to find out how to take a snapshot of two different databases at the exact same instance to prevent inconsistencies inter and intra database. Can I just run mysqldump on both databases at the exact same time and expect the data to be consistent? Thanks for your help.
|
# ? Jul 28, 2010 15:38 |
|
Looks like mysqldump doesn't lock anything itself: http://dev.mysql.com/doc/refman/5.0/en/replication-howto-mysqldump.html they suggest you run FLUSH TABLES WITH READ LOCK; prior to running mysqldump
|
# ? Jul 28, 2010 15:43 |
|
No Safe Word posted:Looks like mysqldump doesn't lock anything itself: Oh I see. Then my question is what FLUSH TABLES WITH READ LOCK; does with insert or update statements. Does it queue them up to be executed after the lock is released? That would be my best case scenario, but I somehow doubt that it would be that easy.
|
# ? Jul 28, 2010 16:00 |
|
All queries ahead of it have to finish before it can lock. Make sure you don't have any big queries running when you do it. edit: This link has more info. butt dickus fucked around with this message at 16:18 on Jul 28, 2010 |
# ? Jul 28, 2010 16:13 |
|
Doctor rear end in a top hat posted:All queries ahead of it have to finish before it can lock. Make sure you don't have any big queries running when you do it. That explains what happens before the lock, but what about what happens during the lock? I just added the read lock and then tried to insert and I got this error. code:
Basically I have a website that is constantly making updates and inserts and I can't bring down the whole site just to create a consistent backup from mysqldump.
|
# ? Jul 28, 2010 16:31 |
|
sonic bed head posted:That explains what happens before the lock, but what about what happens during the lock? I just added the read lock and then tried to insert and I got this error. Well are the tables you are currently using INNODB? Because there is an option to be able to get a consistent backup from innodb tables while they are online using a transaction that isn't possible with the default MyISAM type. Have you ever used the MySQL Administrator tool?
|
# ? Jul 28, 2010 18:49 |
|
If you are running entirely InnoDB, you probably should look at Percona's "XtraBackup". It works without locking the entire database, and hey, it's OSS. And if you aren't running InnoDB, fix that.
|
# ? Jul 28, 2010 21:18 |
|
I am using SQL Server 2005 and I was wondering what the best way to extract the latest auto increment. So let me describe the situation, its very simple, I am adding rows to tables which contain an auto-increment, identity column, "nid". What I want to do is extract the new "nid" value right after the insert. I know there are a few ways to do it. code:
code:
The other way, I think, is to set up a transaction before you insert, then as you end the transaction, extract out the new "nid" but that also seems like too many steps. Anyone have any good ways of doing this. Unfortunately, I can't do a select statement to infer the "nid" based on other values in the table, there is repeated data in the table.
|
# ? Aug 2, 2010 22:31 |
|
|
# ? May 14, 2024 20:31 |
|
cannibustacap posted:So let me describe the situation, its very simple, I am adding rows to tables which contain an auto-increment, identity column, "nid". http://msdn.microsoft.com/en-us/library/ms190315.aspx quote:Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch. Does that make sense? You don't have to set up a stored procedure, necessarily, just read SCOPE_IDENTITY in the same module where you INSERT into the table.
|
# ? Aug 3, 2010 04:31 |