February 11, 2012 at 9:34 pm
CELKO (2/11/2012)
. . . LAG and LEAD came from Oracle and the guys on INCITS H2 never saw MDX. These clauses are shorthand for window clauses”LAG ::= ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
LEAD ::= ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
Joe, I must admit I am not getting this.
Did you want to say --
LAG ::= ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING THAT ONE
LEAD ::= ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING THAT ONE
As you wrote it, possibly in haste - is seems to imply that there might be a difference between the same row referenced twice.
February 11, 2012 at 9:36 pm
Samuel Vella (2/8/2012)
Usually I end up with either CTE's or similar to try and make the SQL more readable.Are there any better work arounds?
Actually, the CTE method you speak of is tried and true and is remarkably fast. The CROSS APPLY method the others suggested is just as fast.
The only place in T-SQL (that I know of) where you might be able to pull off such a thing in the manner you speak of is in an UPDATE where you can mix variables and columns in the same statment. It allows for @variable = columnname = expression, @variable = columnname, and @variable = expression where "expression" can be just about any mix of columnnames, variables, constants, etc, etc. There are caveates to it's use but the careful person can pull off small miracles with it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2012 at 12:48 pm
CELKO (2/12/2012)
CREATE TABLE Foobar( num INTEGER NOT NULL PRIMARY KEY);
GO
INSERT INTO Foobar
VALUES (1), (2), (3), (4), (5);
GO
SELECT num,
MAX(num)
OVER (ORDER BY num
ROWS BETWEEN 1 PRECEDING and 1 PRECEDING )
AS lag
FROM Foobar;
num lag
===========
1NULL
21
32
43
54
That's a great 2012 solution... but this is a 2008 forum and it's just not going to work in 2008. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2012 at 2:13 pm
Got it now. Thanks, Joe!
February 12, 2012 at 2:44 pm
CELKO (2/12/2012)
My wife is an ordained Soto Zen Priest who could beat you with a stick, so I am a pussy cat. I do it to get the attention of the poster.
You get their attention, alright. But just like when being beat with a stick, you have to ask what lesson the subject is really learning.
You have a great mind and many could learn from you if you'd just drop the tough guy act. You'd probably sell more books, as well, and you wouldn't have authors that didn't want to be associated with you and that tough guy act when you asked them to contribute to your book. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2012 at 4:07 pm
CELKO (2/11/2012)
>> A huge assumption and very condescending! <It was neither.
It was, end of story
February 13, 2012 at 6:38 am
CELKO (2/12/2012)
Look at the 2012-02-12 Doonesbury comic strip (http://www.doonesbury.com/strip ) and you will see the real problem in US education.My wife is an ordained Soto Zen Priest who could beat you with a stick, so I am a pussy cat. I do it to get the attention of the poster.
I'm sure she could try. But that's beside the point that the traditional educational methods she uses are specifically designed, by the original Brahminic Teachers, to produce obedient and productive slaves, and to remove any slightest glimmer of innovation, creativity, or personal initiative. If what you want are unthinking slaves, then continue to use those methods. If what you want are people who can actually *think*, learn on their own, innovate creative solutions, et al, then drop that method and find a better way to get people's attention. Like, for example, just answering the question. Works for me. Every time. But I'm weird in that I prefer to work with intelligent, creative peers, rather than cowed, useless slaves. I guess you prefer the later. Your loss.
How many postings do you see that can be summarized as
1. Read BOL or any intro book to me; I am too lazy.
2. My sense of entitlement and unearned self-esteem has been offended by your answer with its research and facts! How dare you!
3. Do my homework (or job) for me. Why should I study?
4. I do not want to learn to do it right!~ Give me a kludge – and I want it NOW!
5. How dare you fail to read my mind! It is wonderful me and I don't have to follow Netiquette!
From you? #5 seems to be about 80% of your posts. From others? About 5% of the posts follow one or more of those.
I am not kidding about getting emails from people who want me to consult for free because they downloaded a pirate copy of one of my books. My personal example of just how fat the entitlement mindset goes was a posting in which the guy had gotten a mis-printed copy of one of my books (duplicate signature bindery error). He expected me to replace it for him; not Elsevier/MKP, not the bookstore. Me, out of my own pocket. I wonder if he also writes Lady GaGa when he gets a scratched CD?
What we have are some really lazy programmers who want to use the newsgroups to do their job or homework for them. Even worse, they want to get an instant college education which is not possible in a short reply. The questions they ask can most often be answered by (1) RTFM, well BOL (2) "Try it and see" (3) a quick Google search in the newsgroup to which they are posting.
What they get instead from most replies is a kludge to get rid of them. If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"
No. You're the guy who tells a newbie "The rocks in your head, you complete moron. Learn your craft! Buy my book! Shut up or my wife will beat you with a stick! Everyone worship ME!" And then you get confused when people think your answers are unhelpful.
Joe, there's no question you know the trade. You do. And I understand you have a lot of ego tied up in your online persona. That's a loss to the community, and I wish it were otherwise, but so be it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 13, 2012 at 8:28 am
You have a great mind and many could learn from you if you'd just drop the tough guy act. You'd probably sell more books, as well, and you wouldn't have authors that didn't want to be associated with you and that tough guy act when you asked them to contribute to your book.
+1
Joe, there's no question you know the trade. You do. And I understand you have a lot of ego tied up in your online persona. That's a loss to the community, and I wish it were otherwise, but so be it.
+1
For God sake... calm down Joe. 🙂
February 13, 2012 at 8:52 am
>>Joe, there's no question you know the trade
But that "trade" is GENERIC, STANDARDs-based SQL. He does NOT know "SQL Server" and over the years has routinely given BAD and often WRONG advice on SQL Server specific forums.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2012 at 6:25 am
TheSQLGuru (2/13/2012)
>>Joe, there's no question you know the tradeBut that "trade" is GENERIC, STANDARDs-based SQL. He does NOT know "SQL Server" and over the years has routinely given BAD and often WRONG advice on SQL Server specific forums.
There's some truth to that. But, of the posts he has here that actually contain any technical advice (as opposed to just ranting), the vast majority are reasonably good advice. Not all of them, but the vast majority. He also gives plenty of advice that has some slight utility (ANSI items like "CURRENT_TIMESTAMP" instead of "GETDATE()" is, in a limited sense, good advice, but is of very limited value, for example). The main problem is that he's trying to overwhelm people, instead of trying to work with them. Very, "my way or the highway, you idiot". Most people, quite correctly, will reject his whole post because of that.
He just doesn't realize that he's not actually accomplishing what he thinks he is. His own ego is getting in the way of his effectiveness. And I consider that too bad.
Even the low-value but true advice would have some value to people, if they didn't end up rejecting him completely because of violations of basic human-relations rules.
Edit: Speaking about him in third-person this way, and accusing him of ego-worship in this manner, will probably alienate him from me just as thoroughly as he alienates others from himself. So my post is, in a way, self-defeating. He'll reject my advice because it violates his asserted self-image, most likey. Very few people have the serenity and wisdom to take something like this as anything other than insulting. I wish I could do better on this, but all the means I know of for actually dealing with this kind of thing require direct personal contact, not public writings.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 14, 2012 at 9:13 am
Don't worry - Joe wouldn't accept your advice or worry with anything negative you say about him in any case! :hehe:
He has gotten a bit better over the years about SQL Server specific help, but still leaves a LOT to be desired.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2012 at 9:15 am
I still think he is a robot... Sent by the ISO.
Jared
CE - Microsoft
February 20, 2012 at 7:15 am
I'm realise I'm posting in an old thread, but I'm genuinely curious about this and maybe someone can point out what I'm missing.
Now try to figure out the meaning of SELECT (a+b) AS c, (d+e) AS a; and change the ordering --- garbage!
Obviously we're talking about a hypothetical programming construct, but it surely seems to be a difficulty that I'm not understanding.
Why couldn't the rows could actually come out (in terms of the original column names), a+b with the new name 'c', d+e with the new name 'a'. Obviously the original column named 'a' is no longer able to be referenced without ambiguity, but surely there could be syntax for that also. Even with old fashioned 1990's era SQL, it can disambiguate multiple columns named 'a' or 'b', why not a simple situation like this?
Now what about the query (d+e) as a, (a+b) as c? I would see THAT construct in each row (using the original row names) d+e, a+b.
So since there are some set oriented implementation internals that prevent this, I feel compelled to ask what is the computational impossibility of this construct? What about set oriented query processing prevents this from happening?
February 20, 2012 at 7:20 am
patrickmcginnis59 (2/20/2012)
I'm realise I'm posting in an old thread, but I'm genuinely curious about this and maybe someone can point out what I'm missing.Now try to figure out the meaning of SELECT (a+b) AS c, (d+e) AS a; and change the ordering --- garbage!
Obviously we're talking about a hypothetical programming construct, but it surely seems to be a difficulty that I'm not understanding.
Why couldn't the rows could actually come out (in terms of the original column names), a+b with the new name 'c', d+e with the new name 'a'. Obviously the original column named 'a' is no longer able to be referenced without ambiguity, but surely there could be syntax for that also. Even with old fashioned 1990's era SQL, it can disambiguate multiple columns named 'a' or 'b', why not a simple situation like this?
Now what about the query (d+e) as a, (a+b) as c? I would see THAT construct in each row (using the original row names) d+e, a+b.
So since there are some set oriented implementation internals that prevent this, I feel compelled to ask what is the computational impossibility of this construct? What about set oriented query processing prevents this from happening?
It's not that they "can't," it's that it was not designed to do that because in SQL we are working with sets of data. We don't want it to the above construct. That's what programming is for 🙂
Jared
CE - Microsoft
February 20, 2012 at 7:29 am
SQLKnowItAll (2/20/2012)
patrickmcginnis59 (2/20/2012)
I'm realise I'm posting in an old thread, but I'm genuinely curious about this and maybe someone can point out what I'm missing.Now try to figure out the meaning of SELECT (a+b) AS c, (d+e) AS a; and change the ordering --- garbage!
Obviously we're talking about a hypothetical programming construct, but it surely seems to be a difficulty that I'm not understanding.
Why couldn't the rows could actually come out (in terms of the original column names), a+b with the new name 'c', d+e with the new name 'a'. Obviously the original column named 'a' is no longer able to be referenced without ambiguity, but surely there could be syntax for that also. Even with old fashioned 1990's era SQL, it can disambiguate multiple columns named 'a' or 'b', why not a simple situation like this?
Now what about the query (d+e) as a, (a+b) as c? I would see THAT construct in each row (using the original row names) d+e, a+b.
So since there are some set oriented implementation internals that prevent this, I feel compelled to ask what is the computational impossibility of this construct? What about set oriented query processing prevents this from happening?
It's not that they "can't," it's that it was not designed to do that because in SQL we are working with sets of data. We don't want it to the above construct. That's what programming is for 🙂
Well maybe I should be clearer. What is it about that construct that is incompatible with set oriented processing?
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply