|
Stephen posted:I've got two MySQL tables: var1ety posted:Aggregating the votes table is probably what's taking so long. The query will out of necessity need to do a full table scan to satisfy the request, which can take a long time when the number of rows is large. You can run the query on its own to verify.
|
# ? Aug 4, 2008 15:09 |
|
|
# ? May 14, 2024 18:54 |
|
How do I make this query: SELECT `ATT_IP`, `ATT_TIMESTAMP`, COUNT(* ) FROM ATTEMPTS GROUP BY `ATT_IP` ORDER BY `ATTEMPTS`.`ATT_TIMESTAMP` DESC Spit out the most recent timestamp associated with each group of ATT_IP?
|
# ? Aug 4, 2008 18:48 |
|
Safety Shaun posted:How do I make this query: Shouldn't that give you a syntax error since you're not grouping by ATT_TIMESTAMP or using ATT_TIMESTAMP in an aggregate operation? I think you want: code:
DECLARE @Temp TABLE ( A_IP nvarchar(100), A_TimeStamp int ) insert into @Temp(A_IP, A_TimeStamp) VALUES ('1', 1) insert into @Temp(A_IP, A_TimeStamp) VALUES ('1', 2) insert into @Temp(A_IP, A_TimeStamp) VALUES ('1', 3) insert into @Temp(A_IP, A_TimeStamp) VALUES ('2', 10) insert into @Temp(A_IP, A_TimeStamp) VALUES ('3', 5) insert into @Temp(A_IP, A_TimeStamp) VALUES ('4', -1) insert into @Temp(A_IP, A_TimeStamp) VALUES ('2', 12) select A_IP, MAX(A_TimeStamp) As 'Most recent', count(*) As 'count' from @Temp group by A_IP order by A_IP
|
# ? Aug 4, 2008 19:06 |
|
chocojosh posted:Here's some sample code I whipped up quickly Thanks chocojosh, based on your code I managed to turn it into SELECT `att_ip` , max( att_timestamp ) AS 'att_mostrecent', COUNT( * ) AS 'att_howmany' FROM attempts GROUP BY `att_ip` ORDER BY `att_timestamp` DESC Which does exactly what I want it to do. I even learned a couple of new things. Mucho appreciated!
|
# ? Aug 4, 2008 19:13 |
|
Let's say I've got two tables (not normalized completely for simplicity): tickets -id ticket_errors (maximum 10 rows per ticket_id) -ticket_id -error (string, length 4) Let's say a ticket has 5 entries in the ticket_errors table, is there a way to join these two tables in SQL and return one row with all the error strings appended in one field? An example would be "001 003 005 006 007". The way I would do this is to get all the rows in whatever I'm using, looping through the rows while adding the error string to a string. However, I'm sure there is a better way to do it that I just can't think of. Any ideas? Edit: Since I need the data in both forms (join table, and one string), and I have the error string available before inserting the data, I'm thinking of just keeping an errors field in the tickets table. Is this sort thing acceptable even though it would mean it isn't normalized? Or would that just be dependent on space constrictions, etc... Scarboy fucked around with this message at 19:12 on Aug 5, 2008 |
# ? Aug 5, 2008 19:09 |
|
What flavor of SQL? There are a few ways to accomplish what you want.
|
# ? Aug 5, 2008 19:52 |
|
Aredna posted:What flavor of SQL? Oracle, I'm not sure about the server version though.
|
# ? Aug 5, 2008 19:56 |
|
Scarboy posted:Oracle, I'm not sure about the server version though. There's a pretty comprehensive thread on the topic on Ask Tom at the following URL: http://asktom.oracle.com/pls/asktom/f?p=100:11:2397328357508087::::P11_QUESTION_ID:229614022562
|
# ? Aug 5, 2008 20:31 |
|
var1ety posted:There's a pretty comprehensive thread on the topic on Ask Tom at the following URL: Nice article, the DECODE trick has been useful to me in the past but it's not what I want this time. The other methods are perfect for what I want to do, but since this data isn't changing (ever!) I think will just go with the errors column and waste some extra space.
|
# ? Aug 5, 2008 20:37 |
|
You can also use the oracle COLLECT statement http://www.oracle-developer.net/display.php?id=306
|
# ? Aug 5, 2008 20:39 |
|
Scarboy posted:Let's say I've got two tables (not normalized completely for simplicity): I threw this together, and it is probably a terrible way to go about this. I am not sure if Oracle has ROW_NUMBER() available. The solution technically works for the situation he described, but the performance will probably be terrible with a lot of rows. Also it is not dynamic, you would always have to know what your maximum number of possibilities would be. Does anyone have a better solution that does not use a cursor? The COLLECT operator looks cool. Is there an MS SQL equivalent? code:
RagingHematoma fucked around with this message at 00:51 on Aug 6, 2008 |
# ? Aug 5, 2008 20:55 |
|
Need some normalization advice: I want to save a setting if people want an email sent daily, weekly, or monthly. Would the proper style be to just keep "1", "2", and "3" in the database, and link them to another table which defines 1 as Daily, 2 as Weekly, etc.? Or would the proper style be to simply store "Daily" in the table and don't use a lookup table? Or, should I use numbers and not even bother with a lookup table, since the possibilities will never change, and it could just be handled in code?
|
# ? Aug 5, 2008 22:05 |
|
Golbez posted:Need some normalization advice: I want to save a setting if people want an email sent daily, weekly, or monthly. Would the proper style be to just keep "1", "2", and "3" in the database, and link them to another table which defines 1 as Daily, 2 as Weekly, etc.? Or would the proper style be to simply store "Daily" in the table and don't use a lookup table?
|
# ? Aug 5, 2008 23:02 |
|
Stephen posted:The general rule I stole from Coding Horrors is "Normalize until it hurts". That is to say, normalize it for now, just to make the code easy to maintain, but in the future, if your list of recipients goes huge, you may want to just reference the ID's directly to improve query performance. I'm somewhat of a noob to normalization, so which would be the best option here? The options: code:
code:
code:
Golbez fucked around with this message at 00:44 on Aug 6, 2008 |
# ? Aug 6, 2008 00:30 |
|
Depending on your RDBMS, you could use an enum field.
|
# ? Aug 6, 2008 07:16 |
|
Real quick question, I'm using MS reporting services to make a front-end to my database. I need to filter results based on Windows Authentication, specifically the user's network alias. I'm told this information is a parameter that is already in Reporting services, but I have no idea where it is or what it is called. Can anyone help me out?
|
# ? Aug 6, 2008 14:53 |
|
Golbez posted:I'm somewhat of a noob to normalization, so which would be the best option here? Nobody should ever use C. Ever. A or B are fine. I would personally use A. Make sure you size your number column appropriately or you will reduce the benefit.
|
# ? Aug 6, 2008 15:11 |
|
I've got a MS Access Database (not my choice, but it is only ~15k records, rarely modified, and never used by more than one user at once) I'm trying to query. If I use code:
code:
code:
I know very little about SQL. Is using "NOT" more complicated for some reason? Or is this just one of many reasons why Access is a bad choice? edit: To clarify a little further. There are 15417 rows in the table mloa_data. The first query returns 12898 rows. I expect the remaining 2519 rows to be returned by both the 2nd and 3rd query. As it stands the 2nd and 3rd queries return 0 rows. edit2: Changing the first query to <> '' instead of Is Not Null gives the same results. Negating this also still gives 0 results. 6174 fucked around with this message at 19:02 on Aug 6, 2008 |
# ? Aug 6, 2008 17:15 |
|
var1ety posted:Nobody should ever use C. Ever. Var1ety is right, never use option C. The first time I ever went on the assumption that "these things will never change" is when things start to become a disaster. Also, keeping things away from the code and on the database level, make application maintenance much easier. I always find it easier to change an entry in a table rather than deploy a new set of code.
|
# ? Aug 6, 2008 17:17 |
|
6174 posted:... The problem is your inner join. You need a left join if you want to use IS NULL or IS NOT NULL correctly.
|
# ? Aug 6, 2008 19:14 |
|
Aredna posted:The problem is your inner join. You need a left join if you want to use IS NULL or IS NOT NULL correctly. Thanks. I just tested it and it works.
|
# ? Aug 6, 2008 20:13 |
|
Random Child posted:Real quick question, I'm using MS reporting services to make a front-end to my database. I need to filter results based on Windows Authentication, specifically the user's network alias. I'm told this information is a parameter that is already in Reporting services, but I have no idea where it is or what it is called. User!UserID
|
# ? Aug 6, 2008 23:32 |
|
var1ety posted:Nobody should ever use C. Ever. How do you mean? I figure a Tinyint would be perfectly fine. Thanks for the advice about skipping C. I was just hoping to avoid making another table (and I see no point in storing "Daily", etc. in the main table, when a single byte can handle it), but then again, I'm sure a basic lookup table of frequencies will be useful in more than one application.
|
# ? Aug 7, 2008 00:05 |
|
Golbez posted:How do you mean? I figure a Tinyint would be perfectly fine. It wasn't a deep insight, I just meant to choose a datatype appropriate for the expected cardinality of the numeric column. Oracle only has the NUMBER data type, and if you do not size it then it defaults to 22 bytes.
|
# ? Aug 7, 2008 01:06 |
|
Golbez posted:I'm somewhat of a noob to normalization, so which would be the best option here? You could also do: code:
|
# ? Aug 7, 2008 04:46 |
In MySQL, when you delete a row from a table it creates 'overhead', which from my understanding is the row still exists, it's just marked as deleted. To clear the overhead, I can do an OPTIMIZE TABLE. Is this supposed to just be done on a weekly basis with a cron job or what?
|
|
# ? Aug 7, 2008 10:01 |
|
Golbez posted:I'm somewhat of a noob to normalization, so which would be the best option here? You should absolutely always use A, complete with referential integrity. Note that this does not mean you have to join in the reference table all the time. It's perfectly fine to write queries like "select * from entity where attribute = 1" when top performance is a concern. The key is that the reference table gives you 1) a place to look up the meanings of the codes and 2) enforcement on using only valid codes. It may not seem like there's anything wrong with using a text field but 6 months later when you don't remember what the valid values are and didn't write them down anywhere, and don't remember what some of them mean, it'll become clear.
|
# ? Aug 7, 2008 17:53 |
|
MoNsTeR posted:You should absolutely always use A, complete with referential integrity.
|
# ? Aug 7, 2008 19:54 |
|
code:
Here is a rundown of what is going on: rpp_raids is the raid table rpp_raid_loot is the raid drop table (all items which dropped) rpp_items is the total item table (no drop information) rpp_requests is the table for all requests. I would like this query to give me a count of total requests and total drops. The drops will work perfectly until I put in the JOIN for the requests, at which point it will then multiply the drops by the requests. I am going nuts trying to figure out why it is doing this, any help would be greatly appreciated
|
# ? Aug 8, 2008 16:41 |
|
Cheesemaster200 posted:... code:
Just a heads up though, this is a pretty slow query in MySQL especially once you start getting a lot of requests and drops.
|
# ? Aug 8, 2008 17:06 |
|
edit NM, I got it, stupid mistake Thanks for the help, by the way Edit Edit: Actually, is there a way so that if request_count comes back as nothing, it displays "0" as default, instead of "null". Thanks! Cheesemaster200 fucked around with this message at 17:42 on Aug 8, 2008 |
# ? Aug 8, 2008 17:23 |
|
Cheesemaster200 posted:Actually, is there a way so that if request_count comes back as nothing, it displays "0" as default, instead of "null". IFNULL({expression}, 0) So for example SUM(columnA) becomes SUM(IFNULL(columnA, 0)) or IFNULL(SUM(columnA), 0) depending on what you're going for. stack fucked around with this message at 18:37 on Aug 8, 2008 |
# ? Aug 8, 2008 18:33 |
|
stack posted:IFNULL({expression}, 0) COALESCE(columnA,0) Right? toby fucked around with this message at 20:49 on Aug 8, 2008 |
# ? Aug 8, 2008 20:44 |
|
I would appreciate some advise on function/algorithm design, no code yet. I'm interested in data mining a table recording web page views. There are nearly 1 million rows (don't ask) but I am only interested in the 80k or so which have nonempty user columns. Another relevant column is page_id. In order to do sane analysis of this data I need to count records for each user, for each page; then I can record in another table the user, page, and the total number of views. However, with several thousand pages and users doing this would seem to require several million of queries with MySQL and other horrors. Is there a more sane way?
|
# ? Aug 8, 2008 21:12 |
|
toby posted:Could also use You could but some might frown upon it. I probably would change it to IFNULL(). (edit: Only if I was in a cranky mood though.) COALESCE is best used when you have many values that might be null and you want the first one that isn't null for example (rough impromptu example): code:
(edit edit: no offense meant if you already understood COALESCE() ) stack fucked around with this message at 21:40 on Aug 8, 2008 |
# ? Aug 8, 2008 21:32 |
|
Carabus posted:I would appreciate some advise on function/algorithm design, no code yet. I'm interested in data mining a table recording web page views. There are nearly 1 million rows (don't ask) but I am only interested in the 80k or so which have nonempty user columns. Another relevant column is page_id. Isn't this code:
|
# ? Aug 8, 2008 21:33 |
|
stack posted:You could but some might frown upon it. I probably would change it to IFNULL(). (edit: Only if I was in a cranky mood though.) Why is IFNULL() superior to COALESCE?
|
# ? Aug 8, 2008 21:36 |
|
Aredna posted:Why is IFNULL() superior to COALESCE? I didn't mean or intend to imply it was. COALESCE(columnA, 0) will do the same exact thing, return 0 if columnA is NULL or columnA if it is not.
|
# ? Aug 8, 2008 21:51 |
|
Aredna posted:Why is IFNULL() superior to COALESCE? In a mixed-version environment where one version does not support COALESCE it might make sense to standardize on operators available on all Production versions of the database. We are forced to, in some situations, ignore new features in our databases so that scripts will run with minimal modification on older versions. My personal preference is to use COALESCE/CASE in all situations and ignore our database's large number of null-handling functions.
|
# ? Aug 8, 2008 21:53 |
|
|
# ? May 14, 2024 18:54 |
|
This is specifically a SQL Server 2005 question: Is it possible to query the SYS objects to get the username of someone who created a stored procedure? Someone wrote something so stupid I need to beat them, severely.
|
# ? Aug 8, 2008 22:04 |