|
Dealing with empty date ranges is actually a huge pain in the rear end with vanilla ANSI SQL. In fact I don't know if there is a way to do it without creating a reference table for your dates. There are often platform specific ways of doing it though, I could help you if you ran Oracle. In your case you need someone who knows MS SQL. Paging Victor to thread 340343284.
|
# ? Feb 29, 2008 02:56 |
|
|
# ? May 13, 2024 11:07 |
|
dest posted:I need a custom Excel SQL query written for my job. The boss wants it, and I don't know a thing about SQL. If I wanted to pay a goon to write it for me, should I post it in here somewhere, or over in SA Mart? So you need weekly sums per-vendor? That should be pretty easy to do. You might get more help if you post the relevant parts of your SQL schema though.
|
# ? Feb 29, 2008 03:33 |
|
Today I was working on my forum project and while adding in the 'Show My Posts' function I got confused as to how I should format my database query. I initially wanted to sort it so it would return an array of topics ordered by the date of your most recent post in the topic. I eventually decided to sort it by the last post from any user in the topic, but I figured I'd still like to learn how to do it the initial way for well... the learning experience. I can't for the life of me seem to figure it out.code:
|
# ? Feb 29, 2008 04:38 |
|
Selecting non-grouped columns in a GROUP BY statement returns inconsistent results. Take the MAX of your user's post dates and order by the result of this aggregate function.
|
# ? Feb 29, 2008 04:46 |
|
Well, I feel silly for having an amazingly simple solution like that. But hey, it works and I learned something from it. Thanks!
|
# ? Feb 29, 2008 05:02 |
|
dest, SQL2000 or SQL2005? If the latter, you can use PIVOT to your advantage. Otherwise, Excel pivot tables would seem to do the trick.
|
# ? Feb 29, 2008 08:25 |
|
Toanek posted:Today I was working on my forum project and while adding in the 'Show My Posts' function I got confused as to how I should format my database query. I initially wanted to sort it so it would return an array of topics ordered by the date of your most recent post in the topic. I eventually decided to sort it by the last post from any user in the topic, but I figured I'd still like to learn how to do it the initial way for well... the learning experience. I can't for the life of me seem to figure it out. What RDBMS let you get away with that? Oracle will throw an exception if you try something like that.
|
# ? Feb 29, 2008 17:17 |
|
Xae posted:What RDBMS let you get away with that?
|
# ? Feb 29, 2008 17:35 |
|
I just checked, and it looks like we're running SQL 2000. Here's the query that gives me all of our active vendors. It also filters out all the vendors that have a last purchase date of 1-1-1900. I have no idea why we have a bunch of vendors with that date, but I have to filter them.code:
code:
|
# ? Feb 29, 2008 17:41 |
|
Victor posted:When in doubt with this question, MySQL. Well, there is another reason to hate the drat thing.
|
# ? Feb 29, 2008 17:54 |
|
Xae posted:Well, there is another reason to hate the drat thing. You don't understand: a blatant disregard for doing things correctly, coupled with being way too lenient about accepted input, is what makes MySQL so easy and is the key to its popularity. And remember, popular = good!
|
# ? Feb 29, 2008 19:53 |
bitprophet posted:You don't understand: a blatant disregard for doing things correctly, coupled with being way too lenient about accepted input, is what makes MySQL so easy and is the key to its popularity. And remember, popular = good! What's a better free alternative?
|
|
# ? Feb 29, 2008 20:25 |
|
fletcher posted:What's a better free alternative? Postgres. Many people additionally consider it to be "freer" (speech-wise, not beer-wise) than MySQL since it has BSD licensing and because its development isn't controlled by a single, private corporation.
|
# ? Feb 29, 2008 20:34 |
brae posted:Postgres. Many people additionally consider it to be "freer" (speech-wise, not beer-wise) than MySQL since it has BSD licensing and because its development isn't controlled by a single, private corporation. What makes it better? Why should I consider switching to it?
|
|
# ? Feb 29, 2008 20:47 |
|
fletcher posted:What makes it better? Why should I consider switching to it? Better is subjective depending on what you want your RDBMS to do. All you have to do is google "postgres vs mysql" and you will find many, many comparisons. Then you can be informed and make up your own mind as to what is the better solution for what you want to do.
|
# ? Feb 29, 2008 21:12 |
|
brae posted:Postgres. brae posted:Better is subjective depending on what you want your RDBMS to do. All you have to do is google "postgres vs mysql" and you will find many, many comparisons. Then you can be informed and make up your own mind as to what is the better solution for what you want to do. QFT. In my own experience - I've used both to an intermediate level both from a developer and sysadmin standpoint - Postgres just feels more mature, streamlined and sensible, versus MySQL feeling, well, like the PHP of databases. But, like brae said, make up your own mind - Google a bit, and if that doesn't give you anything that makes sense, then come back here and I'm sure we'd be glad to give you
|
# ? Feb 29, 2008 23:25 |
|
Oracle 10G XE is free, with the following limitations: 4GB user data 1GB RAM 1 CPU http://www.oracle.com/technology/products/database/xe/index.html
|
# ? Feb 29, 2008 23:54 |
var1ety posted:Oracle 10G XE is free, with the following limitations: Interesting. Since my app is written with PDO, and they have an Oracle driver, does that mean I could drop this in a try it pretty easily? Or trying out postgres? fletcher fucked around with this message at 00:18 on Mar 1, 2008 |
|
# ? Mar 1, 2008 00:03 |
|
bitprophet posted:QFT. A while ago I went and thought of the reasons why MySQL is better than Postgres. I couldn't think of a single one. Postgres is more secure, more stable, and faster. What? Faster? Yes, when it matters. MySQL gives you back that basic query when there is 0 load a split second faster than Postgres, but when the machine is under a heavy load MySQL shits it self.
|
# ? Mar 1, 2008 00:09 |
|
Xae posted:A while ago I went and thought of the reasons why MySQL is better than Postgres. I couldn't think of a single one. Postgres is more secure, more stable, and faster. What? Faster? Yes, when it matters. MySQL gives you back that basic query when there is 0 load a split second faster than Postgres, but when the machine is under a heavy load MySQL shits it self. I don't know. Several companies have scaled in a big way using MySQL (Yahoo) and carefully choosing your engine type per-table in MySQL can help with scaling because you get a little more fine grained control over locking mechanisms. My company uses MySQL and we've found it to be scaleable. Granted, we haven't done a side-by-side with Postgres, but it's certainly fast enough and for certain optimized queries it is very, very fast even under heavy load. But I would agree with bitprophet - on some of the more enterprise-y stuff like stored functions and procedures and replication, well, they work with MySQL, but there are a bunch of caveats (for example, stored procedures can't use result sets generated by other stored procedures, at least in the version of 5.0 we're using, and we recently had to upgrade from an earlier 5.0.x release because there were some nasty replication bugs with stored procedures in the version we were on). The same features feel a lot more robust in Postgres - I mean, MySQL only even got stored procedures in version 5. So, we've gotten this stuff to work in MySQL but it feels a little fragile sometimes. Also, MySQL will let you be sloppier in your SQL syntax (see above, selecting a non-aggregate column value from an aggregate query, what the gently caress) and so, at least in my experience, it is easier to write non-portable SQL in it.
|
# ? Mar 1, 2008 03:26 |
|
I've been using Postgres for the last 6-7 years, and I can't fault it. It's extremely robust, it's fully-featured, and the documentation is excellent. I can't compare performance but all the benchmarks I've seen put it equal to (if not better than) MySQL. My mind boggled when I found out that MySQL only relatively recently introduced transactions. There may be certain situations where MySQL is better, but I think most people would be very happy with Postgres.
|
# ? Mar 1, 2008 04:15 |
|
brae posted:I don't know. Several companies have scaled in a big way using MySQL (Yahoo) and carefully choosing your engine type per-table in MySQL can help with scaling because you get a little more fine grained control over locking mechanisms. My company uses MySQL and we've found it to be scaleable. Granted, we haven't done a side-by-side with Postgres, but it's certainly fast enough and for certain optimized queries it is very, very fast even under heavy load. The tweakers benchmarks are pretty enlightening. In particular on page 6 you can see how badly MySQL just curls into a ball and cries for mommy on a Niagara (SUN) chip. You also see how quickly MySQL hits the limit of its engine and the requests per second start to decline as concurrency increases, where as Postgres holds stable longer. MySQL seems to be popular because it is popular. Things are written for it, because it lets you be sloppy, so people run it.
|
# ? Mar 1, 2008 16:11 |
|
I've got an SQL wonderingness: I've got two tables, staticApts and dynamicApts. Static has the following columns: day, time, comment, details, clientID, staffID dynamic has the following columns: date, time, comment, details, clientID, staffID Static appointments happen every week, so they are referenced by day name and time. Dynamic appointments happen once, so are referenced by date and time. Static appointments is a full table with empty cells (well, comment, client and details are blank), where the data is updated based on user input, blank cells indicate that the slot is free. Dynamic appointments only exist when someone makes one, if the appointment is cancelled then the row is deleted, or if another one is being made over it the row is updated with the new info. I want a page which gets the appointments for a given user today, and only if they have an appointment. I believe I can do a CREATE VIEW and then only pull rows that have staticApts.comment <> " " or something, but I'm not sure. The other way would be to create nested if statements and then query individual rows, which is not desirable because that's 32 queries (or something like that) per person per page load. Essentially: Is it possible to use the CREATE VIEW, and if so how would I join the tables? staticApts has day as a string and dynamicApts has date as a datetime. There are checks to make sure that an appointment can't exist in the same time slot for either the same staff or the same client, so time could be used to join them I think. I've been having a think about it but haven't been able to actually work on it, and I won't be for another day or two, but I was hoping that someone could either reinforce or correct my idea before I attempt it and break a load of things.
|
# ? Mar 2, 2008 21:27 |
|
MORE CURLY FRIES posted:I want a page which gets the appointments for a given user today, and only if they have an appointment. I believe I can do a CREATE VIEW and then only pull rows that have staticApts.comment <> " " or something, but I'm not sure. So you want a view which represents every appointment today? Could you just use a union here? code:
|
# ? Mar 2, 2008 23:36 |
|
Gumbercules posted:So you want a view which represents every appointment today? I wasn't sure if a UNION would do what I want. If it does then that's great, cheers!
|
# ? Mar 3, 2008 03:38 |
Great info about postgres guys, thanks. If I was going to try something new should I go postgres or oracle 10g? I don't think I will be going beyond there computer limitations any time soon.
|
|
# ? Mar 3, 2008 06:02 |
|
fletcher posted:Great info about postgres guys, thanks. If I was going to try something new should I go postgres or oracle 10g? I don't think I will be going beyond there computer limitations any time soon. I would go with Oracle. It defiantly has more "quirks" to familiarize your self with, and it has broader commercial use as well.
|
# ? Mar 3, 2008 13:41 |
|
fletcher posted:Great info about postgres guys, thanks. If I was going to try something new should I go postgres or oracle 10g? I don't think I will be going beyond there computer limitations any time soon. I would use Oracle 10g XE or Microsoft SQL Server XE if I was learning something new. They're both free and mature, with Microsoft SQL Server XE having the advantage of integrating with Visual Studio Express out of the box. My preference is Oracle. I believe that PostgreSQL is still lacking analytic functions with windowing, a feature I would have a very hard time living without.
|
# ? Mar 3, 2008 16:05 |
|
Why don't I want to store passwords using an md5() or SHA1() hash? What's insecure about them that makes everyone suggest going elsewhere?
|
# ? Mar 5, 2008 03:18 |
|
UltraRed posted:Why don't I want to store passwords using an md5() or SHA1() hash? What's insecure about them that makes everyone suggest going elsewhere? They can be cracked, that's what makes them insecure. By adding salt you can make it alot more secure tho.
|
# ? Mar 5, 2008 03:51 |
|
UltraRed posted:Why don't I want to store passwords using an md5() or SHA1() hash? What's insecure about them that makes everyone suggest going elsewhere?
|
# ? Mar 5, 2008 03:55 |
|
noonches posted:They can be cracked, that's what makes them insecure. By adding salt you can make it alot more secure tho.
|
# ? Mar 5, 2008 05:08 |
|
md5 is fairly fast to compute, which is bad for password hashing. some more info
|
# ? Mar 5, 2008 05:59 |
|
Yeah, as far as I know, if you need a hash to generate the same output every time given a specific input, then it can be cracked using lookup tables. Of course salting a hash is not a function of the hash itself to make it more secure, so that kind of means any hash can be cracked given time and effort put into it.
|
# ? Mar 5, 2008 06:03 |
|
Yes, but you can add a 25 character salt. This way, even if your user enters a password of 8 characters, the actual password is 33 characters. That's going to take much more time to crack than an 8 character password. Also, adding a salt makes it much harder for someone to figure out what type of hashing you're using. There are certain popular passwords (e.g. "password"). If someone on your system uses that password and it is unsalted, it is very easy to tell which hashing scheme is used (just analyze a table of different hashing schemes for the value "password").
|
# ? Mar 5, 2008 14:40 |
|
I have a table that contains package, service codes and charges per service. I need to total the charges by account number and I cannot figure out how to do this. This is specific to Access, as the data was provided to me in Access. This is a sample of what I have: I edited out actual account information, and only included a small subset, but I basically need to query this table, and show how the total charges per that account number. Keep in mind that there are going to be hundreds of account numbers, with multiple charges per account number. Example of the Output I'd need from the query: Account Number | Total 8076123456789000 | 77.94 77.94 is the total amount of charges associated with account number 8076123456789000 Any ideas?
|
# ? Mar 5, 2008 17:51 |
|
Use the SUM() function on the charge and group by the account number.
|
# ? Mar 5, 2008 18:00 |
|
I have an Oracle-specific question on a colleague's new "strategy" for SQL "tuning." According to her, using a "somevalue IN ('a', 'b', 'c')" slows down the query's speed (which I believe it really only the case when somevalue is a key field), and the following is her way to speed up queries:quote:I still haven’t had much time to test this in the Oracle environment, but I remembered a trick I used in Informix to make a query run faster<snip>... Any truth to what she is suggesting? It doesn't seem right to me, but I can't put my finger on why. I've found that with many of that office's queries, speed issues tend to be an issue with not using the table indexes in joins, and an obscene use of subqueries and DISTINCTs.
|
# ? Mar 5, 2008 19:34 |
|
Lancer383 posted:Any truth to what she is suggesting? It doesn't seem right to me, but I can't put my finger on why. I've found that with many of that office's queries, speed issues tend to be an issue with not using the table indexes in joins, and an obscene use of subqueries and DISTINCTs. Maybe it depends on something wonky you have going on. On an indexed column it is actually slower for me: code:
code:
|
# ? Mar 5, 2008 20:12 |
|
|
# ? May 13, 2024 11:07 |
|
yaoi prophet posted:<snipped out yaoi's sweet analysis> Thanks for checking this out -- I'm fairly sure that she is delirious -- could you give it a try on a non-indexed column and see if you have similar results? I would do it on my own, but a Plan_Table table isn't set up on our Oracle DBs, and if there's a way to see an explanation plan without a plan_table, I don't know how to do it.
|
# ? Mar 5, 2008 20:33 |