If you know the values are from 1 to 100, then you can do this:
select n.num
from (select d1.d*10 + d2.d as n
from (select 0 as d union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all selec...
@bluefeet Well, sometimes we build the response in our head, without a tool at hand. Maybe it's not the best way to answer a strict SQL question, but for the others it works.
and he answers a lot of questions, one after the other, so I'm guessing that @bluefeet's issue with that is that he obviously doesn't test his answers so he can move to the other question an answer it faster
Just a thought, anyway :). Should be nice to get a piece of code that's parsing and executing well, but the answer is still useful to point an idea (even if it's missing a parenthesis).
Some of more advanced Oracle database features such as session trace do not work properly in Oracle 11g XE 32-bit if installed on Windows 64-bit system. I needed session trace on Windows 7 64-bit.
Apart from that it works well for me in multiple production MS Windows 64-bit systems: Windows Serv...
@Marian Some people just give a negative effect on others. @bluefeet doesn't like "This is not tested, so it may have syntax errors." (me neither. If you can't test, write pseudocode, not SQL), I don't like a certain user that adds the "select" tags in all the questions (he answers), etc. Some smells don't match.
I've chatted with him before, he can be a bit abrasive bit I don't actually think he is irredeemable: I'd prefer we to try and mold him into our image before we resort to bludgeoning him.
I'm not saying we should walk on egg-shells: it's ok to tell him when he's out of line. better not to be personal though eg let's tell him what he's doing is 'dickish' rather than saying he is a dick
Yesterday (April 1st, April Fools Day notwithstanding) we had some high-ranking members of DBA.SE banned from chat by moderators/10k users from other sites that did not take an appropriate amount of time to evaluate the situation. The Heap is a place where many 10k+ users hang out and talk about...
Can I be sure that the result set of the following script will always be sorted like this O-R-D-E-R ?
SELECT 'O'
UNION ALL
SELECT 'R'
UNION ALL
SELECT 'D'
UNION ALL
SELECT 'E'
UNION ALL
SELECT 'R'
Can it be proved to sometimes be in a different order?
@FreshPrinceOfSO Richard's question seems valid enough to me, you could have just said "take it up on mSO if you have a problem with it". He isn't the best communicator and reading the transcript I don't think he deliberately singled you out, but even if he did, who cares: he has no authority here.
@FreshPrinceOfSO he seemed to think the question you linked to was in a different category, or reading between the lines, perhaps he just liked it and didn't want it closed. Neither of which is your fault, you can talk about VtC'ing SO questions wherever you like and it is public knowledge we do it in here
Try removing all of the ALLs, for example. Or even just one of them. Now consider that the type of optimization that has to happen there (and many other types) will also be possible when the SELECT queries are actual queries against tables, and are optimized separately. Without an ORDER BY, order...
@bluefeet There was an assumption made in the SQL Server 200 code that meant using GROUP BY resulted in implicit ordering according to your GROUP BY clause. When they release 2005, many many customers complained the query processor was broken because results were coming back unsorted.
+1 - We definitely get good migrations on DBA. Part of our situation is also an active chat room community with a lot of users having migration rights on SO - if we see something that belongs on our site we can collectively bring it over ourselves without having to involve the perpetually overworked SO mods. — JNKJan 31 at 14:38
Is upvoting duplicate questions really following the guidelines of the all mighty alt text?
The question shows research effort; it is useful and clear
I raise this question after recently receiving this comment on a duplicate question.
@FreshPrinceOfSO Then close-vote on that basis, ple...
I ran across the mysql-like and I am not sure if it is needed. You would think these questions would be tagged with mysql and like but the "LIKE" tag wiki states:
This tag is not for the SQL reserved keyword LIKE.
I am guessing that since the like specifically states this is not for SQL th...
@FreshPrinceOfSO So what stops you? :-) If it's not really identical, word by word, you would deserve a +, from my point of view.
Now, really, if all the answers are in the same time-frame, than I usually up-vote the correct ones. If one comes after 3 days and says the same as an existing answer, then I won't up-vote.
@FreshPrinceOfSO I often add my own answer when the other answers answer the question but maybe leave out some details. Also a different query pattern may be better or worse and I'd like to add my own alternative. I even do this when a similar answer has already been accepted, since the more ways something is said (even if it boils down to the same thing), the more likely the post will come up in a search, and the more likely a future reader will find value.
@FreshPrinceOfSO World's too bad. I just like to spread love :-). Now there are more ways to skin a cat.. as @AaronBertrand said. So I like to thank them all for the effort.
@FreshPrinceOfSO I don't see any identical answers in this case. Yes we are all saying the same thing but we are saying it differently, quoting/linking to different things, showing different counter-examples, etc.
@FreshPrinceOfSO if we all just answered "No, you can't guarantee it." then you'd have a case I think.
sigh
+1 @AaronBertrand ALTHOUGH THERE'S NO NEED TO SHOUT MR BERTRAND — whytheq1 min ago
The urban dictionary defines burniate as:
A complete meltdown.
I say we completely melt down this tag as it's simply not mysql specific. Otherwise, we may as well create a tag for each DBMS and it's respective operators.
Last week, I vented about the DBAs inability to handle permissions and used anul and fracking in the same sentence and external mods came in, cleaned my posts and kicked me out for 30
The log shows IO DBName is Frozen follow by thaw but yet my database schedule backup task which normally been running isn't since the network team are doing backs (not database backups, according to them) on the database server, using ArcServ 16.5. They run their backup utility around 11:00pm or...
There are a number of tags for SQL key words. And I think that makes sense, mostly. However, many of those keywords are not very selective or descriptive by themselves as they are very common words. As SO grows, many of them are competing with other fields. I started editing a new tag wiki on sql...
Part of your problem is that you are using dynamic sql and you want to insert that into a temp table for use later on. Here are the issues with that:
The temp table cannot be created before because the number of columns is unknown.
A temp table created before the dynamic SQL will be out of sco...
Thanks for the help. I figured I had something wrong with that. Do you think that is how they will have to do these joins? A global temp for each query?
@AaronBertrand that BEGIN ... END requirement I noted was a supposition on my part. I now realize that anything past the AS is included in the text of the stored proc. Thanks for pointing that out!
and that wasn't my downvote on your answer, by the way!
I don't really know if this would be an acceptable question for the site, but I was wondering if there's a best practice for passing a parameter into a stored procedure to tell it which queries to execute, and if so what it is. For example, passing a parameter containing something like 0010110 to tell it to run the 3rd, 5th, and 6th queries and return them
(I don't think I want to create a separate SP for each procedure since another parameter is a huge list of comma-separated values which gets parsed into a #temp table, and I don't want to do that parsing more than once)
@AaronBertrand I'm working with a dynamic number of queries, where users supply which sets of data to return by checking boxes, and I'm using SQL Server 2005 so table-valued parameters are not available
And my application which uses the procedure obtains the data into a .Net DataSet, so to access the data I need to know which tables are in the data set, and in what order they're located
I guess I could post a question here (or on SO), but I wasn't sure if it would be on-topic or not
@bluefeet I've retagged all mysql-like to sql-like and have requested that OP's with at least 5 upvotes on questions with like-operator request a synonym be created.
I personally don't like aliasing tables all over the place because:
you can forget what you are doing
a pinch of aliasing is like salt in cooking; it can go a long way.
aliasing is sloppy and can be hard to read for the next person.
An alias is only required if you refer to the table twice, to...
@FreshPrinceOfSO well thanks, I have heard what people say.....I suck
In standard SQL, the result of a union all is not guaranteed to be in any order. So, something like:
select 'A' as c union all select 'B'
Could return two rows in any order (although, in practice on any database I know of, 'A' will come before 'B').
In SQL Server, this turns into an executio...
Hard to read for the next person? You would rather read/write tablename. in front of every object in your select statement than a simple alias. What if the column name is ambiguous and you're creating a view/sproc/select statement and clarify what the column name actually means?? What if you're building a report sproc?? This statement impossibly incorrect. — Zane10 secs ago
You can forget what you are doing makes even less sense. If you forget what your alias is then scroll back up and look what you put there! Point 5 learning the database structure. I still have to make all the correct joins. You still have to know how the data fits together. What are you talking about sir? — Zane15 secs ago
@bluefeet if you come to a new site and make bold and sweeping assertions about something as basic as aliasing in SQL server without giving the appearance of having actually USED aliasing or had to maintain any complex code, you deserve all the downvotes that get tossed your way
@lance Please don't get offended by the downvotes. We have nothing against you, we only disagree with the answer. Welcome to StackOverflow. — ypercube48 secs ago
@bluefeet I'm judging the answer on it's merits not how long the user has been here. I don't want there to be any chance that someone mistakes that answer for information.
@JNK Not for you, and neither for me. But I've seen a lot of high rep users taking downvotes very personal, so a new user may be prone to do it too
and if he doesn't, then there is always the embarrased feeling. (I would've hide and possibly never made another contibution on SO if my first answer had 6 downvotes)
If you know the values are from 1 to 100, then you can do this:
select n.num
from (select d1.d*10 + d2.d as n
from (select 0 as d union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all selec...
Can I be sure that the result set of the following script will always be sorted like this O-R-D-E-R ?
SELECT 'O'
UNION ALL
SELECT 'R'
UNION ALL
SELECT 'D'
UNION ALL
SELECT 'E'
UNION ALL
SELECT 'R'
Can it be proved to sometimes be in a different order?
Is upvoting duplicate questions really following the guidelines of the all mighty alt text?
The question shows research effort; it is useful and clear
I raise this question after recently receiving this comment on a duplicate question.
@FreshPrinceOfSO Then close-vote on that basis, ple...
I ran across the mysql-like and I am not sure if it is needed. You would think these questions would be tagged with mysql and like but the "LIKE" tag wiki states:
This tag is not for the SQL reserved keyword LIKE.
I am guessing that since the like specifically states this is not for SQL th...
I personally don't like aliasing tables all over the place because:
you can forget what you are doing
a pinch of aliasing is like salt in cooking; it can go a long way.
aliasing is sloppy and can be hard to read for the next person.
An alias is only required if you refer to the table twice, to...
